Home > T-SQL > Dynamically getting the execution status of a SQL Server Agent Job

Dynamically getting the execution status of a SQL Server Agent Job

 

This problem seems trivial but there is no straight forward way to get this information. If you have tried the INSERT into EXEC command to insert the results of the sp_help_job procedure into a temporary table like below then you are familiar with the below error.

insert into #jobstatus
execute msdb..sp_help_job

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

So you can not directly insert the results to a temp table from this procedure. Here is a trick I have used using the OPENROWSET to get around with the INSERT EXEC problem. This query results can be stored into a temporary table or a table variable using the INSERT INTO or SELECT INTO command which can be used for further processing.

   select name, case when current_execution_status = 1 then ‘Executing’
           
when current_execution_status = 2 then ‘Waiting For Thread’
           
when current_execution_status = 3 then ‘Between Retries’
           
when current_execution_status = 4 then ‘Idle’
           
when current_execution_status = 5 then ‘Suspended’
           
when current_execution_status = 6 then ‘[Obsolete]’
           
when current_execution_status = 7 then ‘PerformingCompletionActions’
       
else NULL end as [status]  from
   openrowset
(‘SQLNCLI’, ‘Server=(local);Trusted_Connection=yes;’,‘EXEC msdb..sp_help_job’)
 

The OPENROWSET is one of the workarounds for the nested INSERT EXEC problem and many other solutions are proposed here (Thanks to Kalman for the workarounds).

Hope it helps

🙂 ranjith 🙂

Advertisements
  1. Swapna
    June 4, 2010 at 12:22 pm

    This was a good realization for me while struggling to get job specifics.

    Only issue was that, it needed ‘Ad Hoc Distributed Queries’ to be set on the server, which needed a little fight as it is disabled by default – due to security reasons.

  1. April 23, 2010 at 8:39 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: