[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2060
  • Last Modified:

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 ?
1 Solution
It is possible by making sp_help_job as function
see the below example

create table #tab1 (name varchar(100), owner varchar(20), object_type varchar(20))

insert into #tab1 exec sp_help
exec sp_help
create function fntiedtest() returns @testtable table (name varchar(100), owner varchar(20), object_type varchar(20))
INSERT @testtable select au_id as name, au_fname as owner, phone as object_type from pubs..authors

insert into #tab1 select  * from dbo.tiedtest()

- Edward

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now