View Job History in SQL Server

- 
create procedure util.ViewJobHistory
(
    @ExecutionDate date = null
    ,@JobName sysname = null
)
as
begin
    declare @executionDateInt int;
    set @executionDateInt = year(@ExecutionDate) * 10000
        + month(@ExecutionDate) * 100
        + day(@ExecutionDate);
 
    select
        JobName
        ,JobSuccess
        ,RunDateFormatted + ' '
            + left(RunTimePadded, 2)
            + ':' + substring(RunTimePadded, 3, 2)
            + ':' + right(RunTimePadded, 2) [RunDateTime]
        ,DurationSeconds + (60 * DurationMinutes) + (60 * 60 * DurationHours) [TotalDurationSeconds]
    from
    (
        select
            sj.name [JobName]
            ,case sjh.run_status
                when 1 then 'Success'
                else 'FAIL'
            end [JobSuccess]
            ,left(cast(sjh.run_date as varchar(8)), 4)
                + '-'
                + substring(cast(sjh.run_date as varchar(8)), 5, 2)
                + '-'
                + right(cast(sjh.run_date as varchar(8)), 2) [RunDateFormatted]
            ,right('000000' + cast(sjh.run_time as varchar(6)), 6) [RunTimePadded]
            ,sjh.run_duration / 10000 [DurationHours]
            ,(sjh.run_duration - (sjh.run_duration / 10000 * 10000)) / 100 [DurationMinutes]
            ,(sjh.run_duration - (sjh.run_duration / 100 * 100)) [DurationSeconds]
        from msdb..sysjobs sj
        join msdb..sysjobhistory sjh on sj.job_id = sjh.job_id
        where isnull(@executionDateInt, sjh.run_date) = sjh.run_date
        and isnull(@JobName, sj.name) = sj.name
        and sjh.step_id = 0
    ) x
end;
go

Comments

Post a Comment

Popular posts from this blog

C# Record Serialization

-
Image
With .NET 5, you get a new concept called record . Lots of cool things, one of which is that you can serialize and deserialize with JSON just like a class, except you don't need to define properties one at a time, or even the body of the type. Apparently XML serialization doesn't quite work, so stick with JSON. View code on GitHub
Read more

Versioned content in MVC

-
When you add a script or stylesheet to your HTML page, those requests can be cached by the browser, potentially providing outdated content to the browser. If you’re not using a bundler or anything fancy like that, then the only way to prevent this problem is to create a brand new URL whenever your script file or stylesheet changes. This is easily accomplished with a querystring, so if your app was previously using app.js?v=1 and you change that to app.js?v=2, the browser will definitely not use the cached version, and will instead make a new request to the server, and pull the latest one. Using the following technique, you can guarantee that you’ll always pull the latest script and stylesheet when working in dev, and when in production, you can define a “version key”, possibly in a config file or database, and if you ever update just the static content, you would only need to update that key, and it would force all browsers to pull new content. public static class HtmlExtensions {...
Read more

Add timestamp to photo using ImageMagick

-
rem dptnt.com/2009/04/how-to-add-date-time-stamp-to-jpeg-photos-using-free-software/ @ ECHO OFF SETLOCAL FOR / R %%G IN ( *.jpg ) DO CALL :process "%%G" GOTO :end :process SET _inname = %1 identify - format %%w %_inname% > dttmpfile set / p width = < dttmpfile Set / a pointsize = %width% / 50 rem echo ZZ >> dttempfile DEL dttmpfile ECHO Processing %_inname% ... convert %_inname% - gravity SouthEast - font Arial - pointsize %pointsize% - fill orange - annotate +%pointsize%+%pointsize% "%%[exif:DateTimeOriginal]" %_inname% EXIT / B :end
Read more