An INSERT EXEC statement cannot be nested. Dealing with error

Question

Not duplicate. The problem here is it has different structure. and I have some restrictions of what I can change and what I can't. Here I have exact example, which other tickets don't. OPENROWSET cannot be applied either, because test1 loops through all dbs (using EXEC sp_MSforeachdb @sql). And I can't use it, because in OPENROWSET you have to specify from which db you are running procedure

Suppose I have these two SPs:

create procedure test1
@ProcName  varchar(155)
as
begin

if OBJECT_ID('tempdb..#testt') is not null drop table #testt
create table #testt1(a int, b int)

insert #testt1
exec @ProcName

select * from #testt1
end

create procedure test2  
as
begin

declare @sql varchar(155)

if OBJECT_ID('tempdb..#testt2') is not null drop table #testt2
create table #testt2(a int, b int)

select @sql = 'select 1 as a,2 as b'

insert #testt2
exec (@sql)

select * from #testt2

end

when I run exec test1 @ProcName = 'Test2', it returns error:

An INSERT EXEC statement cannot be nested.

The problem here is that I can't stop using insert exec in test2. And test1 should have @ProcName as a variable

What is the least painless way to resolve this issue?

Possible duplicate of [Errors: "INSERT EXEC statement cannot be nested." and "Cannot use the ROLLBACK statement within an INSERT-EXEC statement." How to solve this?](http://stackoverflow.com/questions/3795263/errors-insert-exec-statement-cannot-be-nested-and-cannot-use-the-rollback-s) — Jacob H, May 18 '17 at 15:02
@JacobH, not duplicate. The problem here is it has different structure. and I have some restrictions of what I can change and what I can't. Here I have exact example, which other tickets don't — rakamakafo, May 18 '17 at 15:08
Well every problem has a different structure. Have you tried using OPENROWSET that was recommended in the link I posted? — Jacob H, May 18 '17 at 15:09
I agree with @JacobH - this is a duplicate. Author does not write why he need insert...exec. There are many ways how to share data between procedures, not `OPENROWSET` only — Mikhail Lobanov, May 18 '17 at 15:21
@MikhailLobanov, I made it easy and simple in above representation. But here are details: I can't stop using insert exec in test2, because I use there many variables in a query, which I attach and detach, and then manipulate that temp table. — rakamakafo, May 18 '17 at 15:24
@Sher have you read that article? You write about `OPENROWSET` in your question, but there are another ways to deal with this problem — Mikhail Lobanov, May 18 '17 at 15:30
@MikhailLobanov, I did. Nothing helped. At least from my point of view. That is the reason why I'm posting question here. — rakamakafo, May 18 '17 at 15:45
@Sher What problem you have with Process-Keyed Tables? You cannot rewrite your procedures? — Mikhail Lobanov, May 18 '17 at 15:55
@MikhailLobanov, I know community rules, and I try to follow them. You think that I didn't research well, I did. If you are going to post here unconstructive and unrelated critique, please, just leave this page — rakamakafo, May 18 '17 at 16:03
@Sher I think what you are looking for may not exist. Your best options have been suggested (temp table, TVP, OPENROWSET and OPENQUERY, etc.) Maybe try using a non-temporary table? Your next step is probably a CLR if *none* of these options work. — Jacob H, May 18 '17 at 20:20

score 1 · Accepted Answer · answered Jul 05 '17 at 21:51

1

Putting insert statement inside test2 procedure solved problem

answered Jul 05 '17 at 21:51

rakamakafo

1,144
5
21
44

score 0 · Answer 2 · answered May 21 '17 at 18:03

Try replacing the temporary table in the second procedure with a table variable, then use OPENQUERY (as explained in http://www.sommarskog.se/share_data.html), like this:

CREATE DATABASE Database1
CREATE DATABASE Database2
GO
DECLARE @ProcedureTest2 NVARCHAR(MAX)
SET @ProcedureTest2='create procedure test2  
as
begin
SET NOCOUNT ON 

declare @sql varchar(155)

DECLARE @testt2 TABLE (a int, b int)

select @sql = ''select 1 as a,2 as b''

insert @testt2
exec (@sql)

select * from @testt2

end'

DECLARE @SQL NVARCHAR(MAX)
SET @SQL='USE Database1 EXEC('''+REPLACE(@ProcedureTest2,'''','''''')+''')'
EXEC(@SQL)
SET @SQL='USE Database2 EXEC('''+REPLACE(@ProcedureTest2,'''','''''')+''')'
EXEC(@SQL)

GO
EXEC sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @@servername

GO
CREATE procedure test1
@ProcName  varchar(155)
as
begin

if OBJECT_ID('tempdb..#testt1') is not null drop table #testt1
create table #testt1(a int, b int)

DECLARE @Databases TABLE (DBName sysname PRIMARY KEY)
INSERT INTO @Databases VALUES ('Database1')
INSERT INTO @Databases VALUES ('Database2')

DECLARE Databases CURSOR LOCAL READ_ONLY FOR
SELECT * FROM @Databases d

DECLARE @DBName sysname
OPEN Databases

WHILE 1=1 BEGIN
    FETCH NEXT FROM Databases INTO @DBName
    IF @@FETCH_STATUS<>0 BREAK

    DECLARE @SQL1 NVARCHAR(500), @SQL2 NVARCHAR(1000)
    SET @SQL1='EXEC '+QUOTENAME(@DBName)+'.dbo.'+QUOTENAME(@ProcName)
    SET @SQL2='SELECT * FROM OPENQUERY(LOCALSERVER, '+QUOTENAME(@SQL1,'''')+')'

    PRINT @SQL2
    INSERT INTO #testt1
    EXEC (@SQL2)
END

CLOSE Databases
DEALLOCATE Databases

select * from #testt1
end

GO
--EXEC [Database1].dbo.[test2]

--SELECT * FROM OPENQUERY(LOCALSERVER, 'EXEC [Database1].dbo.[test2]')

EXEC dbo.test1 'test2'

GO
EXEC sp_dropserver 'LOCALSERVER'
DROP PROCEDURE test1
DROP DATABASE Database1
DROP DATABASE Database2

An INSERT EXEC statement cannot be nested. Dealing with error

2 Answers2

Linked