Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

An INSERT EXEC statement cannot be nested

Posted on 2008-06-20
1
Medium Priority
?
4,701 Views
Last Modified: 2012-05-05
When I try to capture the result set from sp_help_job using the following statement, it works fine:

INSERT dbname.dbo.tablename
EXEC [LinkedServer\Instance].msdb.dbo.sp_help_job @execution_status = 1
GO

If I try the following statement, it fails:

INSERT dbname.dbo.tablename
EXEC msdb.dbo.sp_help_job @execution_status = 1

with this error message:

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.

I'm guessing that this is because sp_help_job calls sp_get_composite_job_info which has an INSERT...EXEC statement itself.  Why does this work for the linked server, in that case?  Is it something to do with the way the result set is returned?  How can the result set be captured on a local server?  I've tried setting up a linked server pointing back at itself but that doesn't work.

Thanks.  
0
Comment
Question by:mr_omid
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 

Accepted Solution

by:
mr_omid earned 0 total points
ID: 21847859
I found that this works:
INSERT dbo.TableName
SELECT * FROM OPENROWSET('sqloledb', 'server=servername;trusted_connection=yes', 'exec msdb.dbo.sp_help_job @execution_status=1')
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question