Checks whether supplied Agent Job has completed successfully on the given date

On 13 Apr, 2015

By : Charith Silva

No Comments

Sometimes you need to get to know the status of particular SQL Server Agent job on a given date. Below Stored Procedure can be used to check whether supplied SQL Agent Job has completed successfully on the given date.

CREATE PROCEDURE [dbo].[usp_Check_SQLAgentJob_Run] (@jobname AS NVARCHAR(MAX), @date DATETIME)

AS

DECLARE @job_rundate NVARCHAR(20)

SET @job_rundate = CAST(YEAR(@date) AS VARCHAR(4)) + RIGHT('0'+ CAST(MONTH(@date) AS VARCHAR(2)), 2) + RIGHT('0'+ CAST(DAY(@date) AS VARCHAR(2)), 2)

IF @job_rundate IN (
SELECT h.run_date
FROM msdb.dbo.sysjobhistory AS h
INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
WHERE j.name = @jobname
AND h.step_id = 0
AND h.run_status = 1
)

BEGIN
Print '"' +@jobname +'" SQL Agent job has completed Successfully on : ' + CAST(@job_rundate as NVARCHAR(20))
END

ELSE
BEGIN
Print '"' +@jobname +'" SQL Agent job has not completed Successfully on : ' + CAST(@job_rundate as NVARCHAR(20))
END

CREATE PROCEDURE [dbo].[usp_Check_SQLAgentJob_Run] (@jobname AS NVARCHAR(MAX), @date DATETIME)

DECLARE @job_rundate NVARCHAR(20)

SET @job_rundate = CAST(YEAR(@date) AS VARCHAR(4)) + RIGHT('0'+ CAST(MONTH(@date) AS VARCHAR(2)), 2) + RIGHT('0'+ CAST(DAY(@date) AS VARCHAR(2)), 2)

IF @job_rundate IN (

SELECT h.run_date

FROM msdb.dbo.sysjobhistory AS h

INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id

WHERE j.name = @jobname

AND h.step_id = 0

AND h.run_status = 1

)

BEGIN

Print '"' +@jobname +'" SQL Agent job has completed Successfully on : ' + CAST(@job_rundate as NVARCHAR(20))

END

ELSE

BEGIN

Print '"' +@jobname +'" SQL Agent job has not completed Successfully on : ' + CAST(@job_rundate as NVARCHAR(20))

END

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva