检查SQL Server Job状态代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】检查SQL Server Job状态

作者:DTC2 / 发布于2015/8/10/ 793


	方法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

试试其它关键字

　Job状态　

同语言下

可能有用的

DTC2贡献的其它代码(12)