SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An INSERT EXEC statement cannot be nested


An INSERT EXEC statement cannot be nested

Author
Message
PRAMANA.DBA
PRAMANA.DBA
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5469 Visits: 1047
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]
SrcName
SrcName
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1419 Visits: 400
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
Kenny Jozi
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1986 Visits: 1707
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
michal.lisinski
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6725 Visits: 1348
hi

Imho the [Rep_Accuracy_Achievement_Analysis_Report] returns different number of columns.

Br.
Mike
Mark Cowne
Mark Cowne
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26418 Visits: 27770
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
enriquezreyjoseph
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1105 Visits: 191
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
PRAMANA.DBA
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5469 Visits: 1047
Code is too big, it having around 900 lines .
but inner side also post data to temp table from sp result.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search