利用视图查看作业的执行情况代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】利用视图查看作业的执行情况

作者:dezai / 发布于2014/4/16/ 1054

-- 监控作业运行状况
create view Job_status1
with encryption
as
SELECT 作业的名称 = name,
 对作业的说明 = description,
 计划运行作业的下一个日期 = (SELECT top 1 left(left(next_run_date,4)+'-'+right(next_run_date,4),7)+'-'+right(next_run_date,2)
 FROM msdb.dbo.sysjobschedules
 WHERE job_id = sysjobs.job_id),
 计划运行作业的时间 = (SELECT top 1 left(left(left('000000',6-len(next_run_time))+ltrim(next_run_time),2)+':'+right(left('000000',6-len(next_run_time))+ltrim(next_run_time),4),5)+':'+right(left('000000',6-len(next_run_time))+ltrim(next_run_time),2)
 FROM msdb.dbo.sysjobschedules
 WHERE job_id = sysjobs.job_id),
 作业的执行状态 = CASE (SELECT top 1 run_status
 FROM msdb.dbo.sysjobhistory
 WHERE job_id = sysjobs.job_id
 ORDER BY instance_id DESC)
 WHEN 0 THEN '失败'
 WHEN 1 THEN '成功'
 WHEN 2 THEN '重试'
 WHEN 3 THEN '已取消'
 WHEN 4 THEN '正在进行中'
 END,
 作业或步骤开始执行的日期 = (SELECT top 1 left(left(run_date,4)+'-'+right(run_date,4),7)+'-'+right(run_date,2)
 FROM msdb.dbo.sysjobhistory
 WHERE job_id = sysjobs.job_id
 ORDER BY instance_id DESC),
 作业或步骤开始的时间 = (SELECT top 1 left(left(left('000000',6-len(run_time))+ltrim(run_time),2)+':'+right(left('000000',6-len(run_time))+ltrim(run_time),4),5)+':'+right(left('000000',6-len(run_time))+ltrim(run_time),2)
 FROM msdb.dbo.sysjobhistory
 WHERE job_id = sysjobs.job_id
 ORDER BY instance_id DESC),
 执行作业或步骤所花费的时间 = (SELECT top 1 left(left(left('000000',6-len(run_duration))+ltrim(run_duration),2)+'小时 '+right(left('000000',6-len(run_duration))+ltrim(run_duration),4),5)+'分钟 '+right(left('000000',6-len(run_duration))+ltrim(run_duration),2)+'秒'
 FROM msdb.dbo.sysjobhistory
 WHERE job_id = sysjobs.job_id
 ORDER BY instance_id DESC)
FROM msdb.dbo.sysjobs;
go

试试其它关键字

　视图　

同语言下

可能有用的

dezai贡献的其它代码(1065)