- --方法1
- exec msdb.dbo.sp_help_job @execution_status=1
- --方法2
- select distinct j.Name as "Job Name", j.description as "Job Description", h.run_date as LastStatusDate, h.run_time as LastStatusTime, h.run_duration as LastStatusDuration,
- case h.run_status
- when 0 then 'Failed'
- when 1 then 'Successful'
- when 3 then 'Cancelled'
- when 4 then 'In Progress'
- end as JobStatus
- from msdb..sysJobHistory h, msdb..sysJobs j
- where j.job_id = h.job_id
- and h.step_id = 1
- and h.run_date =
- (select max(hi.run_date) from msdb..sysJobHistory hi where h.job_id = hi.job_id)
- and h.run_time =
- (select max(hj.run_time) from msdb..sysJobHistory hj where h.job_id = hj.job_id)
- order by 1
- --方法3
- select distinct cat.name as "Category", j.Name as "Job Name", j.description as "Job Description",
- h.run_date as LastStatusDate, h.run_time as LastStatusTime, h.run_duration as LastStatusDuration,
- case h.run_status
- when 0 then 'Failed'
- when 1 then 'Successful'
- when 3 then 'Cancelled'
- when 4 then 'In Progress'
- end as JobStatus
- from msdb..sysJobHistory h, msdb..sysJobs j, msdb..syscategories cat
- where j.job_id = h.job_id and
- j.category_id = cat.category_id
- and h.step_id = 1
- and h.run_date =
- (select max(hi.run_date) from msdb..sysJobHistory hi where h.job_id = hi.job_id)
- and h.run_time =
- (select max(hj.run_time) from msdb..sysJobHistory hj where h.job_id = hj.job_id)
- order by 1,3
- --方法4
- Select
- [Job Name] = j.name
- , [Job Description] = j.description
- , [LastRunDate] = h.run_date
- , [LastRunTime] = h.run_time
- , [JobStatus] = Case h.run_status
- When 0 Then 'Failed'
- When 1 Then 'Successful'
- When 3 Then 'Cancelled'
- When 4 Then 'In Progress'
- End
- ,[OrderOfRun] = Rank() Over(Partition By j.job_id Order By h.run_date Desc,h.run_time Desc)
- From
- msdb.dbo.sysjobhistory h
- Left join msdb.dbo.sysjobs j On j.job_id = h.job_id
- Where h.step_id=0 --only look @ Job Outcome step
- Order By [Job Name] desc,Rank() Over(Partition By j.job_id Order By h.run_date Desc,h.run_time Desc) Asc
- --该片段来自于http://www.codesnippet.cn/detail/1108201513390.html
来源: http://www.codesnippet.cn/detail/1108201513390.html