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”’)