ERROR: An INSERT EXEC statement cannot be nested.

[DTAC-SKTOCCO]

Hello,

Version: MS SQL Server 2000 Developers Edition running SP3a

I get the following error when I try to execute sp_help_job and insert it into a temp table so I have access to the data for reference purposes:

ERROR:
Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
An INSERT EXEC statement cannot be nested.

Here is my code that I am using:
********************************
create table #JobTemp(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)

insert into #JobTemp exec sp_help_job

************************************

Does anyone know a way to execute this so that I would have access to the data that is returned for my jobs??

Thank you kindly.

Sam Tocco
D-TAC Corporation

[Steve Kass]

Sam,

Apparently sp_help_job uses INSERT .. EXEC, but you could try this
workaround:

...
insert into #JobTemp
select * from openquery(desktop,'set fmtonly off; exec
msdb..sp_help_job')

You'll have to have set up desktop (or whatever name you want) as a
loopback linked server. I don't think this is really what OPENQUERY was
intended for, so use this at your own risk.

Steve Kass
Drew University

Post by DTAC-SKTOCCO
Hello,
Version: MS SQL Server 2000 Developers Edition running SP3a
Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
An INSERT EXEC statement cannot be nested.
********************************
create table #JobTemp(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)
insert into #JobTemp exec sp_help_job
************************************
Does anyone know a way to execute this so that I would have access to the data that is returned for my jobs??
Thank you kindly.
Sam Tocco
D-TAC Corporation