Home Forums SQL Server 2005 T-SQL (SS2K5) List job name which didn't run today but supposed to run today. RE: List job name which didn't run today but supposed to run today.

  • will this help:

    ;with LatestJobs AS

    (SELECT Job_id, step_id, max(convert(varchar(8),run_date)+RIGHT('000000'+CONVERT(varchar(6), run_time),6)) As LastRun

    from msdb.dbo.sysjobhistory

    group by Job_id, step_id)

    select j.job_id, j.name, next_run_date, next_run_time

    from msdb.dbo.sysjobs j

    join msdb.dbo.sysjobhistory h on h.job_id = j.job_id

    join LatestJobs lj on lj.job_id = j.job_id and lj.step_id = h.step_id and lj.LastRun = convert(varchar(8),run_date)+RIGHT('000000'+CONVERT(varchar(6), run_time),6)

    join msdb.dbo.sysjobschedules s on s.job_id = j.job_id

    where h.run_date < s.next_run_date and h.run_time < s.next_run_time

    and convert(date,convert(varchar(8),ISNULL(NULLIF(s.next_run_date,0),19000101)),112) < convert(date, getdate())

    please do not hesitate to work and / or refine this further if it does not meet all your requirements

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”