Getting the status (including In Progress) of SQL Job in SQL Server 2005+

I find it amazing how many posts there on forums, etc where people want to know what the status of SQL Server Jobs are and how difficult it was. The SysJobHistory table is good if you don’t care about jobs that are In Progress, but if you do even though it has a status of In Progress it will never show that status because the SysJobHistory record is not created until the job is Completed.

Here is the code if you don’t care about In Progress and want to use the SysJobHistory table. This would basically mean you want the last status of a job that completed.

select * from msdb.dbo.sysJobHistory h, msdb.dbo.sysJobs j
where j.job_id = h.job_id and h.run_date =
(select max(hi.run_date) from msdb.dbo.sysJobHistory hi where h.job_id = hi.job_id)

The SysJobHistory table is pretty well documented at here.

Since SQL Server 2005, if you want to get the current execution status of a job you can use the following:

exec msdb.dbo.sp_help_job

This is simple and easy. The problem some people seem to have with it is that it is a stored procedure and can’t use it like a table. It does already allow for a bunch of parameters that are very much like using a where clause. For example if you want to get all In Progress jobs it is simple:

exec msdb.dbo.sp_help_job @execution_status = 1

Still, you are limited to what they provide as parameters. Luckily as with any stored procedure you can work around this.

This site has lots of examples of how to take results from stored procs and select from them.

One example of this (if OPENROWSET is available on your installation of SQL Server) is:

SELECT *  FROM OPENROWSET ( 'SQLOLEDB','SERVER=.;Trusted_Connection=yes','EXECUTE msdb.dbo.sp_help_job');

If you don’t have OPENROWSET as an option then you will have to work a bit harder.

My first thought was do to something like

insert into Results exec msdb.dbo.sp_help_job --@execution_status = 1

That gets the data, but also generates an error which I really don’t think is a good thing.

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

So, I had to look to another solution. Unfortunately it is undocumented, but it works.

declare @Results table(
    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, -- bool
    request_source int not null,
    request_source_id sysname collate database_default null,
    running int not null, -- bool
    current_step int not null,
    current_retry_attempt int not null,
    job_state int not null )

insert @Results exec master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = ''

select * from @Results

This works well UNLESS you want to connect to another server via a linked server since this is really calling a DLL underneath the stored procedure. If you try that you will get the error:

Msg 7411, Level 16, State 1, Line 1
Server 'myserver' is not configured for RPC.

I am pretty sure I can get by that, but I think that is opening up a security hole.

Conclusion:

I find this topic so frustrating. I really want it to be simpler and supported, but the best I can do is use the unsupported option since my SQL Server configuration cannot be changed. I wished all SQL Server stored procedures worked more like Table-valued functions. In the end, I created another table that each of the jobs from the different servers log when the start and stop. I can then do some queries to figure out the status (including In Progress) of the jobs. Seems like there should be a better way than opening up rpc, using openquery, etc. If I can implement it, Microsoft certainly could. Please Microsoft, please. What am I missing?