Monday, October 24, 2011

Find failed SQL jobs in one click

--The code to convert run_date and run_time into an actual--datetime is NOT my code, and I can't remember what blog post--I found it from. But it's not mine.select sj.name, sjs.step_name,CONVERT ( DATETIME, RTRIM(run_date))+(run_time * 9+ run_time % 10000 * 6 + run_time % 100 * 10) / 216e4from msdb.dbo.sysjobs sjinner join msdb.dbo.sysjobsteps sjson sj.job_id = sjs.job_idinner join msdb.dbo.sysjobhistory sjhon sjs.job_id = sjh.job_idand sjs.step_id = sjh.step_idwhere sjh.run_status = 0and datediff (hour, (CONVERT ( DATETIME, RTRIM(run_date))+(run_time * 9+ run_time % 10000 * 6 + run_time % 100 * 10) / 216e4), current_timestamp) <= 24

No comments:

Post a Comment