Having ‘INSERT EXEC nested issues’?

Without manual intervention, one level of INSERT…EXECUTE is the limit

An INSERT EXEC statement cannot be nested.

It is telling you is that you can’t have a proc that does an INSERT-EXEC operation call another proc that also does an INSERT-EXEC operation.

Two basic steps to solve this:

1) Create a #Temp table or @Table var
2) OPENROWSET your stored procedure data into it


INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')



Or check out one of these links:
http://sqlserverplanet.com/sql/insert-stored-procedure-results-into-table/
http://blog.coryfoy.com/2005/07/inserting-the-results-of-a-stored-procedure-to-a-temp-table/
http://barry-king.com/2008/05/06/insert-into-temporary-table-from-stored-procedure/
http://www.informit.com/articles/article.aspx?p=25288&seqNum=6








Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s