Showing posts with label failed sql agent jobs sql server 2008. Show all posts
Showing posts with label failed sql agent jobs sql server 2008. Show all posts

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