Redirect the output from the SP msdb.dbo.sp_help_job

[SteelMillDon]

Hi,
I have tried all I know and cannot get the output from the
msdb.dbo.sp_help_job SP into a temp table. I keep getting this error:
Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.

Here is my code:

/*
CREATE TABLE
#LabVIEW_IsIBA_JobRunning(
job_id uniqueidentifier
,originating_server nvarchar(30)
,name sysname
,enabled tinyint
,description nvarchar(512)
,start_step_id int
,category sysname
,owner sysname
,notify_level_eventlog int
,notify_level_email int
,notify_level_netsend int
,notify_level_page int
,notify_email_operator sysname
,notify_netsend_operator sysname
,notify_page_operator sysname
,delete_level int
,date_created datetime
,date_modified datetime
,version_number int
,last_run_date int
,last_run_time int
,last_run_outcome int
,next_run_date int
,next_run_time int
,next_run_schedule_id int
,current_execution_status int
,current_execution_step sysname
,current_retry_attempt int
,has_step int
,has_schedule int
,has_target int
,type int
);
*/
/*
job_id
,originating_server
,name
,enabled
,description
,start_step_id
,category
,owner
,notify_level_eventlog
,notify_level_email
,notify_level_netsend
,notify_level_page
,notify_email_operator
,notify_netsend_operator
,notify_page_operator
,delete_level
,date_created
,date_modified
,version_number
,last_run_date
,last_run_time
,last_run_outcome
,next_run_date
,next_run_time
,next_run_schedule_id
,current_execution_status
,current_execution_step
,current_retry_attempt
,has_step
,has_schedule
,has_target
,type
)
*/
INSERT #LabVIEW_IsIBA_JobRunning
EXEC msdb.dbo.sp_help_job
@job_name = N'Get Level 2 Oracle Data For Cognex',
@job_aspect = N'JOB',
@execution_status = 1;

[Erland Sommarskog]

SteelMillDon (SteelM...@noemail.noemail) writes:
> I have tried all I know and cannot get the output from the
> msdb.dbo.sp_help_job SP into a temp table. I keep getting this error:
> Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
> An INSERT EXEC statement cannot be nested.

Apparently sp_help_job uses INSERT-EXEC itself, which means that this
road is blocked to you.

The two options I can see is to write a CLR procedure that gets the result
from sp_help_job into a dataset that you then can write in a temp table, or
try the OPENQUERY trick. The latter would be your only option if you are
on SQL 2000.

I discuss both methods in a little more detail in
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[SteelMillDon]

Thanks,
I will look into

[SteelMillDon]

Thanks The OPENQUERY did work for me.