;WITH CTE_SQLAGENT_JOB_STATUS
AS (
SELECT DISTINCT NAME AS [JobName]
,s.step_id ,s.step_name
,CASE
WHEN [Enabled] = 1 THEN 'Enabled' ELSE 'Disabled'
END [JobStatus]
,CASE
WHEN SJH.run_status = 0 THEN 'Failed'
WHEN SJH.run_status = 1 THEN 'Succeeded'
WHEN SJH.run_status = 2 THEN 'Retry'
WHEN SJH.run_status = 3 THEN 'Cancelled'
WHEN SJH.run_status = 4 THEN 'In Progress'
ELSE 'Unknown'
END [JobOutcome]
,CONVERT(VARCHAR(8), sjh.run_date) [RunDate]
,CONVERT(VARCHAR(8), STUFF(STUFF(CONVERT(TIMESTAMP, RIGHT('000000' + CONVERT(VARCHAR(6), sjh.run_time), 6)), 3, 0, ':'), 6, 0, ':')) RunTime
,RANK() OVER ( PARTITION BY s.step_name ORDER BY sjh.run_date DESC
,sjh.run_time DESC ) AS rn
,SJH.run_status
FROM msdb..SYSJobs sj
INNER JOIN msdb..SYSJobHistory sjh ON sj.job_id = sjh.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON sjh.job_id = s.job_id
AND sjh.step_id = s.step_id
WHERE (sj.NAME LIKE 'JOB NAME')
AND sjh.run_date = CONVERT(CHAR, getdate(), 112)
)
SELECT [JobName]
FROM CTE_SQLAGENT_JOB_STATUS
WHERE rn = 1
AND run_status NOT IN (1,4)
and [JobName] = 'Job Name
No comments:
Write commentsPlease do not enter spam links