September 25, 2013 at 2:04 am
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]
September 25, 2013 at 2:44 am
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,
September 25, 2013 at 2:50 am
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
September 25, 2013 at 2:59 am
hi
Imho the [Rep_Accuracy_Achievement_Analysis_Report] returns different number of columns.
Br.
Mike
September 25, 2013 at 3:16 am
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/61537September 25, 2013 at 3:39 am
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
September 25, 2013 at 4:17 am
Code is too big, it having around 900 lines .
but inner side also post data to temp table from sp result.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy