olebrum030800
asked on
insert into #tab exec sp_help_job
I,m running on sql-server 2000 in Query Analyzer.
I create the following table:
create table #tab (
job_id int,
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)
Then I run the following query :
insert into #tab exec sp_help_job @description = 'Autogenerert'
Result :
Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
An INSERT EXEC statement cannot be nested.
(0 row(s) affected)
-------------------------- ----
I can see that the "exec sp_help_job @description = 'Autogenerert' " find one row, but why can I not populate the table #tab with the result from the procedure ? Is it possible to do this in another way ?
I create the following table:
create table #tab (
job_id int,
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)
Then I run the following query :
insert into #tab exec sp_help_job @description = 'Autogenerert'
Result :
Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info,
An INSERT EXEC statement cannot be nested.
(0 row(s) affected)
--------------------------
I can see that the "exec sp_help_job @description = 'Autogenerert' " find one row, but why can I not populate the table #tab with the result from the procedure ? Is it possible to do this in another way ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.