[sourcecode language=”sql”]
/*
Stored procedure to run the last run time and run satus of a SQL job.
SQL Version: 2005+
Last modified on 2013-07-03 by Nick Xu
–Usage
–returns the last successful run time
exec sp__GetLastJobRunTimeStatus @jobname = N’syspolicy_purge_history’, @status = N’success’
–returns the last failed runtime
exec sp__GetLastJobRunTimeStatus @jobname = N’syspolicy_purge_history’, @status = N’fail’
*/
use master
go
if exists (select * from sysobjects where name = N’sp__GetLastJobRunTimeStatus’ and type = N’P’)
begin
drop proc sp__GetLastJobRunTimeStatus
end
go
create proc sp__GetLastJobRunTimeStatus
@jobname as varchar(100) = N’syspolicy_purge_history’,
@status as varchar(10) = N’success’
as
begin
declare @run_status as int
if (upper(@status) = N’FAIL’)
begin
set @run_status = 0
end
else
begin
set @run_status = 1
end
SELECT j.[name] as JobName,
"RunSatus" =
case
when jh.run_status = 1 then N’SUCCESS’
else N’FAIL’
end,
MAX(CAST(STUFF(STUFF(CAST(jh.run_date as varchar),7,0,’-‘),5,0,’-‘) + ‘ ‘ + STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),’ ‘,’0′),5,0,’:’),3,0,’:’) as datetime)) AS [LastRunTime]
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh
ON jh.job_id = j.job_id AND jh.step_id = 0
WHERE j.[name] = @jobname
GROUP BY j.[name], jh.run_status
HAVING jh.run_status = @run_status
end
go
[/sourcecode]
Leave a Reply