Error: INSERT EXEC statement cannot be nested (again)

[DiggerDog]

OK

I've written a simplier form of what I wish to do and still get the errors. I create stroed proc sp_test which returns the value 1

I then create sp_test2 which executes isp_test into the temporary table #temp2 and then selects the results from this table

Finally I create sp_test3 which executes isp_test2 into the temporary table #temp3 and then selects the results from this table


The code is as follows:

CREATE Procedure dbo.sp_test
As
select 1
go

CREATE Procedure dbo.sp_test2
As
create table #temp2
(
num int
)
insert into #temp2
exec sp_test
select * from #temp2
drop table #temp2
go


CREATE Procedure dbo.sp_test3
As
create table #temp3
(
num int
)
insert into #temp3
exec sp_test2
select * from #temp3
drop table #temp3
go

when I execute isp_test3 i get the error

Server: Msg 8164, Level 16, State 1, Procedure isp_test2, Line 13
An INSERT EXEC statement cannot be nested.

Server: Msg 266, Level 16, State 2, Procedure isp_test2, Line 18
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.



see thread183-521130 INSERT EXEC statement cannot be nested
for my original posting

 

[tlbroadbent]

SQL Server cannot perform nested Insert Exec statments. You'll have to revise your code. If using SQL Server 2000, you may be able to use User-Defined functions that return tables instead of using the stored procedures.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SemperFiDownUnda]

There you have it....from the master. Funny that we, me included, couldn't accept what SQL server has been telling us from the beginning in black and white 8).