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
Posts
