Friday, 24 May 2013

SQL: List Schedule Jobs in MSSQL

--VIEW SQL JOB HISTORY, WORK SQL 2000

USE msdb
Go 
SELECT h.step_id, j.name JobName,h.step_name StepName, 
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate, 
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime, 
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded' 
when 2 then 'Retry' 
when 3 then 'Cancelled' 
when 4 then 'In Progress' 
end as ExecutionStatus, 
h.message MessageGenerated
,STUFF(STUFF(REPLICATE('0',6-LEN(next_run_time)) + convert(VARCHAR(6),next_run_time),3,0,':'),6,0,':') As NextRunTime
,s.*
FROM sysjobhistory h inner join sysjobs j
INNER join sysjobschedules s on s.job_id = j.job_id
ON j.job_id = h.job_id
WHERE CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 103) > '2012-May-21' --DOES NOT QUITE COMPARE DATES CORRECTLY!
AND h.run_status = 0
ORDER BY h.run_date, j.name, h.run_time