Yes, you clearly stated that but what's your beef with "undocumented"? This extended proc is documented somewhere (inside Microsoft)...it's just not available to us, though I'm sure some one could get their hands on it if they persisted and had the right contacts. And fear of change!? In SQL Server land??!! All the !documented! stuff has changed from version to version and sometimes between service packs. I had to change the code below (which I no longer use) because of changes bewteen SQL 7.0 and SQL 2k. (BTW MS also recommends that we don't read system tables directly either.)
Use msdb
go
if exists (select *
from dbo.sysobjects
where id = object_id(N'[dbo].[usp_JobStatus]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
Drop procedure [dbo].[usp_JobStatus]
Go
Create procedure [dbo].[usp_JobStatus]
@jobname sysname = null,
@datetime datetime = null,
@status int = -1 Output
as
/*******************************************************************/
--Name : dbo.usp_JobStatus
--Server : Generic
--Database : msdb
--
--Description : Returns the run_status of the most recent step 0
-- of a job passed by name, otherwise returns -1
-- : Works in ISQL/W, ISQL, OSQL & Query Analyzer
--Date : 04/25/2003
--Author : Clint Herring
--
--History : Date Initials Modifications
-- 07/15/2004 CH Updated to take into account
-- differences between SQL 7.0 and SQL 2k
/*******************************************************************/
/*
Run_Status
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
5 = Hasn't run since midnight of parameter date
6 = Job doesn't exist
*/
Set NOCOUNT On
declare @date int
declare @time int
declare @jobid varchar(36)
declare @stepid int
select @jobid = job_id from msdb.dbo.sysjobs where name = @jobname
If @jobid is null return 6
select @date = convert(int, convert(varchar(8), @datetime, 112))
select @time = datepart(hour, @datetime)*10000 +
datepart(minute, @datetime)*100 +
datepart(second, @datetime)
select @stepid = step_id
from msdb.dbo.sysjobhistory
where job_id = @jobid
and instance_id =
(select max(instance_id) from msdb.dbo.sysjobhistory where job_id = @jobid)
If (@stepid = 0 or @stepid is null)
Begin
select @status =
Isnull((select b.run_status
from msdb.dbo.sysjobs a
join msdb.dbo.sysjobhistory b
on a.job_id = b.job_id
where name = @jobname
and step_id = 0
and instance_id =
(select max(instance_id)
from msdb.dbo.sysjobhistory c
where c.step_id = 0
and c.job_id = a.job_id
and c.run_date >= @date
and c.run_time + c.run_duration +
case when (c.run_time + c.run_duration)%100 >60 then 40 else 0 end +
case when (c.run_time + c.run_duration)%10000 >6000 then 4000 else 0 end
>= @time)),5)
End
Else
Begin
Set @status = 4
End
go
/* Example usage
use msdb
If (select object_id('tempdb.dbo.#tmpstatus')) > 0
drop table #tmpstatus
Create table #tmpstatus (name sysname null, status int null)
declare @status int
declare @date datetime
declare @name sysname
set @date = '07/16/2004' --date part prior to last run
select @name = min(name) from sysjobs
Print 'The following jobs statuses are as of ' + convert(varchar(26),@date,113)
while @name is not null
begin
exec msdb.dbo.usp_JobStatus @name, @date, @status output
insert into #tmpstatus values (@name, @status)
select @name = min(name) from sysjobs where name > @name
end
select convert(varchar(40),name) [job name],
case when status = 0 then 'Failed'
when status = 1 then 'Succeeded'
when status = 2 then 'Retry'
when status = 3 then 'Canceled'
when status = 4 then 'In progress'
else 'has not run since ' + convert(varchar(26),@date,113)
end [status]
from #tmpstatus
*/