Categories
Scripts

Script to Get Job Details in SQL 2005

This script will get job details for all the jobs

Applies to

  • SQL Server 2000
  • SQL Server 2005
  • SQL Server 2008


Script

select b.name,a.step_name,c.message,
case c.run_status when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
else 'NO STATUS' END as 'status'
,substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.run_time), 6), 1, 2)
+ ':'+ substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.run_time), 6) ,3 ,2)
+ ':'+ substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.run_time), 6) ,5 ,2) as run_time
,substring (right (stuff (' ', 1, 1, '00000000') + convert(varchar(8),c.run_date), 8), 1, 4)
+ '/' + substring (right (stuff (' ', 1, 1, '00000000') + convert(varchar(8), c.run_date), 8),5 ,2)
+ '/' + substring (right (stuff (' ', 1, 1, '00000000') + convert(varchar(8),c.run_date), 8),7 ,2) as run_date
from msdb..sysjobsteps a, msdb..sysjobhistory c,msdb..sysjobs b
where a.step_id=c.step_id and a.job_id=c.job_id
and a.last_run_date=c.run_date and a.last_run_time=c.run_time and a.job_id=b.job_id
order by run_date, run_time

Usage

Copy the script and execute in query analyzer or management studio

 

Sample Output

Name Step_Name Message Status Run_time Run_date
test test Executed as user: WIN-JALZJSOFWJMSYSTEM. The process could not be created for step 1 of job 0xAE0CB76412BB6C478A87F81465F33C34 (reason: %1 is not a valid Win32 application). The step failed. Failed 20:25:01 2008/11/03

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*