11 June 2014 developing tutorial sql sql server developing

Where I work the main DB engine is SQL Server 2005, today we had to find a way to check the status of a job started from a stored procedure (following this tutorial).

The tutorial show the usage of OPENROWSET for checking the job status, but for many reasons we could not use that function in our environment so we had to find another way

After many experiments I've written a query that could replace the OPENROWSET without incurring in the "nesting problem" of calling sp_help_job directly.

declare @CurrentJobs table
(
[Job ID] uniqueidentifier,
[Last Run Date] varchar(255),
[Last Run Time] varchar(255),
[Next Run Date] varchar(255),
[Next Run Time] varchar(255),
[Next Run Schedule ID] varchar(255),
[Requested To Run] varchar(255),
[Request Source] varchar(255),
[Request Source ID] varchar(255),
[Running] varchar(255),
[Current Step] varchar(255),
[Current Retry Attempt] varchar(255),
[State] varchar(255)
)
insert into @CurrentJobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''

select *
from @CurrentJobs cj 
join msdb.dbo.sysjobs sj on cj.[Job ID]= sj.job_id
OUTER APPLY
        (
        SELECT  TOP 1 *
        FROM    msdb.dbo.sysjobhistory hj
        WHERE   cj.[Job ID]= hj.job_id
        ORDER BY
                [run_date] DESC, [run_time] DESC
        ) h
WHERE name='my_job_name'

Hope someone could find this useful

Bye
Kirys