SQL Server Job Execution Status Output

It is not possible to use INSERT….EXEC to call sp_help_job to output the results into a table for querying e.g you want to get the execution status of a job using sp_help_job through some form of automation.The error you will receive is this:

An INSERT EXEC statement cannot be nested.

This is because sp_help_job already is using insert…exec inside the procedure (it calls other procedures within) .

The only way to successfully do that is by using OPENROWSET. You will have to enableAd-Hoc distributed Queries (via sp_configure) to be able to do this.

Here is an example of how to use this:

SELECT * FROM   OPENROWSET(‘SQLNCLI’, ‘SERVER=<my_sql_server>;TRUSTED_CONNECTION=YES;’, ‘EXEC msdb.dbo.sp_help_job @job_name = ”<my_sql_agent_job_name>”, @job_aspect = ”JOB”’)

This entry was posted in T-SQL, Tips. Bookmark the permalink.