Testing for Job Execution Status

Whoops, looks like it got shredded. Anyone who wants a "clean" copy can email me at dan_wunder@[removethispart]hotmail.com

This seems to work for me in SQL 2000. Sorry, comments removed so I could fit it in one reply.
-Decayschampion

create procedure sp_jobstate (@job_id UNIQUEIDENTIFIER)
as
begin
if not exists (select * from msdb..sysjobs where job_id=@job_id)
begin
select 'Invalid job id specified'
return(0)
end
set nocount on
declare @is_sysadmin int
declare @job_owner sysname
declare @running int
declare @status int

CREATE TABLE #xp_results (
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0),@job_owner = SUSER_SNAME()
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
select 'Job: '+sj.[name]+
case xp.running
when 0 then ' is not running'
when 1 then ' is running'
else ' is in an unexpected state'
end,
char(13)+'Status: '+
case xp.job_state
when 1 then 'Executing'
when 2 then 'Waiting For Thread'
when 3 then 'Between Retries'
when 4 then 'Idle'
when 5 then 'Suspended'
when 6 then '[obsolete]'
when 7 then 'PerformingCompletion'
end
from msdb..sysjobs sj join #xp_results xp on (sj.job_id=xp.job_id)
drop table #xp_results
end

I get the same error. It would be nice if the author had posted some sample code.

I tried this and got this Error

Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
An INSERT EXEC statement cannot be nested.

(0 row(s) affected)


create table test_job (
job_id UNIQUEIDENTIFIER null ,
job_name sysname null,
job_aspect VARCHAR(9) null ,
-- Job set parameters
job_type VARCHAR(12) null ,
owner_login_name sysname null ,
subsystem NVARCHAR(40) null ,
category_name sysname null ,
enabled TINYINT null ,
execution_status INT null ,
date_comparator CHAR(1) null ,
date_created DATETIME null ,
date_last_modified DATETIME null,
description NVARCHAR(512) null )


go
insert into test_job
exec msdb..sp_help_job