INSERT EXEC cannot be nested. Dealing with error
Not duplicated. The problem here is that it has a different structure. and I have some limitations of what I can change and what I cannot. Here I have an exact example that other tickets don't have. OPENROWSET cannot be applied because test1 goes through all the dbs ( using EXEC sp_MSforeachdb @sql)
. And I cannot use it because in OPENROWSET you need to specify which db you run the procedure from
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 an error:
INSERT EXEC statement cannot be nested.
The problem here is that I cannot stop using insert exec
the test2
. And test1 must have @ProcName as a variable
What is the least painless way to solve this problem?
source to share
Try to replace the temporary table in the second table variable procedure, then use OPENQUERY (as described 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
source to share