An INSERT EXEC statement cannot be nested

Question

Post reply

An INSERT EXEC statement cannot be nested

PRAMANA.DBA

SSCertifiable

Points: 5507
More actions
September 25, 2013 at 2:04 am

#278374

Hi All,
i am trying to post sp result to temp table then am getting this error, How can i resolve this issue?.
Msg 8164, Level 16, State 1, Procedure Rep_Accuracy_Achievement_Analysis_Report_PRR, Line 461
An INSERT EXEC statement cannot be nested.
.
Ex:
insert into #Result(ID,Name,Count)
exec [Rep_Accuracy_Achievement_Analysis_Report]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SrcName Default port Points: 1460 More actions · Answer 1

if you can post procedure code.

First error is:

In your stored procedure [Rep_Accuracy_Achievement_Analysis_Report]

you have insert data from another stored procedure in temp table. that is not allowed and you attempt to bubbleup data from a chain in stored procedures. Only one insert into exec is allow at time.

You can solve this with: create procedure with output parameter, create table valued function or with global temp table e.t.c

second error is consequence of first,

Kenny Jozi SSCrazy Points: 2028 More actions · Answer 2

create a temp table and openrowset your stored procedure data into it

INSERT INTO #tmp_table

SELECT *

FROM

OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')

SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 3

hi

Imho the [Rep_Accuracy_Achievement_Analysis_Report] returns different number of columns.

Br.

Mike

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

Great article here

http://www.sommarskog.se/share_data.html

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

enriquezreyjoseph Ten Centuries Points: 1291 More actions · Answer 5

PRR.DB (9/25/2013)
Hi All,
i am trying to post sp result to temp table then am getting this error, How can i resolve this issue?.
Msg 8164, Level 16, State 1, Procedure Rep_Accuracy_Achievement_Analysis_Report_PRR, Line 461
An INSERT EXEC statement cannot be nested.
Msg 213, Level 16, State 7, Procedure Rep_Accuracy_Achievement_Analysis_Report_PRR, Line 793
Column name or number of supplied values does not match table definition.
Ex:
insert into #Result(ID,Name,Count)
exec [Rep_Accuracy_Achievement_Analysis_Report]

please let us see the code?..thanks...hehe

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 6

Code is too big, it having around 900 lines .

but inner side also post data to temp table from sp result.