An INSERT EXEC statement cannot be nested

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

An INSERT EXEC statement cannot be nested

Author

Topic

dbapac999
Starting Member

4 Posts

Posted - 2010-07-14 : 16:46:11

I am trying to insert the result set returned from a stored proc into a runtime table (or temp table or cursor, does not matter), code snippet and error follows:

Insert @pub_state
exec sys.sp_replmonitorhelppublication @publisher = 'pcctdbcwt', @publication = 'CAS_PAC_Meters'

error message:
Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested.

I have seen several posts that say the way to deal with this is to SET FMTONLY OFF doing something like this:

select * from
openrowset('SQLOLEDB','pcctdbcwt';'UID’;'PWD',
'
SET FMTONLY OFF;
EXEC SP_HELPDB
')
This works and the result set from the SP is returned. However, if I try this:
select * from
openrowset('SQLOLEDB','pcctdbcwt';'UID';'PWD',
'
SET FMTONLY OFF;
EXEC sys.sp_replmonitorhelppublication @publisher = 'pcctdbcwt', @publication = 'CAS_PAC_Meters')'

It starts complaining about the systax of the SP parameters such as:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'pcctdbcwt'.

I believe the actual issue here is that this ends up being a quoted literal string:
'
SET FMTONLY OFF;
EXEC sys.sp_replmonitorhelppublication @publisher = 'pcctdbcwt', @publication = 'CAS_PAC_Meters')

And thus the SP parameters never evaluate to the supplied values. I just can’t see a way around this…

Bottom line seems to be the openrowset-SET FMTONLY OFF technique will not work with a SP requiring parameters.
I just cannot seem to find a way to get the results of this SP into a table!!! Any help GREATLY appreciated.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-15 : 06:08:26

Based on my assumption you can insert the result set returned from a stored procedure into a run time table
For example,

CREATE TABLE #temp(ReOpenDate DATETIME,ReopenTime VARCHAR(5),CloseDate DATETIME,CloseTime VARCHAR(5))
INSERT INTO #temp
EXEC uspGetCloseTime @ID,@Date,@TimePart

Following is the sample of "uspGetCloseTime" procedure's select script,

SELECT @DateTobeChecked AS ReOpenDate,@DayStart AS ReOpenTime,@DateTobeChecked+1 as CloseDate,REPLACE(@DayCloseTime,'+1','') as CloseTime

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

dbapac999
Starting Member

4 Posts

Posted - 2010-07-15 : 19:08:17

That works with some SPs but not this one unfortunatly because it calls another SP and this is what causes it to fail with the "cannot be nested" error.

D.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources