INSERT EXEC Statement cannot be nested

Question

i have a 3 or 4 cursors, and in the inner cursor i am inserting into a table from a sproc. i keep getting the error

 

An INSERT EXEC statement cannot be nested.

 

heres the actual insert code:

 

set @SQLString = 'EXEC ScoresGetlines '+cast(@customerID as char(10))+',' + cast(@programId as char(10))+',' + '"'+ @period +'",NULL,NULL,0'

INSERT INTO reportData

exec (@sqlString)

 

ive tried just a simple :

insert into reportdata

exec scoreGetLines @customerId,@programID...........

 

that still doesnt work. same error. how can this be sorted

Answer 1

Hi,

look this article here from Erland, that´ll help you:

http://www.sommarskog.se/share_data.html

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Answer 2

Hello,

 

I had the same problem. I've solved it using ths suggestion of my friend Maciek. If You have stored procedure which uses another stored procedure you can get this error. To solve it change that nested stored procedures to functions. That has worked for me.

 

Radosław Jaszek

Answer 3

It is one of the new behavior change in SQL Server 2005. Here i given the sample workaround, no need to convert your sp into function....

 

Before execute the following code you have to change few settings (script given as bellow).

 

Prerequisite

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE

go

EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1'

go

RECONFIGURE WITH OVERRIDE

go

EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE

go

 

 

 

Work-Around:

Work-Around

Create Proc InsertIntoSp

as

Begin

            Set NoCount ON

            Declare  @Script Table

            (

                        Lines varchar(max)

            );

            Insert Into @Script

                        Exec sp_helptext 'sp_helptext'

            Select * From @Script;

End

 

Go

 

Create Table #MyScriptTable

            (

                        Lines varchar(max)

            );

 

--Fail

Insert Into #MyScriptTable

            Exec master..InsertIntoSp

/*

Error Message:

Msg 8164, Level 16, State 1, Procedure InsertIntoSp, Line 10

An INSERT EXEC statement cannot be nested.

*/

 

--Workaround

Insert Into #MyScriptTable

SELECT a.*

FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=YourServerName; UID=sa; PWD=PASSWORD’,  'Exec master..InsertIntoSp') AS a;

 

Select * from #MyScriptTable

 

 

Answer 4

I like the OPENROWSET method, but I work in an environment that does not give me permissions to execute this.  I saw where SQL 2008 has a TABLE type to pass through stored procedures, but we are stuck on 2005 for the next year at least.  In my project, I am reporting state level results, then summarizing the last reported results from the state level on a US level -- ALWAYS the same columns.  It would be convenient to call my state level stored procedure to retrieve the MAX(receipt date) from each state.  In doing so I tried to INSERT-EXEC from an 'outer' US level stored proc to an 'inner' one that returns results from each state.  My other option was that I was going to copy the state level proc to a new one and add an outer loop to loop through all the states.  If logic ever changed, I'd have to change it in two places.

 

If I had permissions, the OPENROWSET looks like it would work.  Since I don't, would there be anything similar to table types as parameters?

 

Thanks

Answer 5

i'm a little late to this ballgame; i came across this while i was researching a project...

 

one question... why do you need to do all of this? can you not instead do this:

 

Create Proc InsertIntoSp

as

Begin

            Set NoCount ON

--            Declare  @Script Table

--            (

--                        Lines varchar(max)

--            );

--            Insert Into @Script

                        Exec sp_helptext 'sp_helptext'

--            Select * From @Script;

End

 

Go

 

now just:

 

InsertInto #MyScriptTable

exec InsertIntoSp

 

---------------------------------------------------------

 

in other words, modify the first SP so that instead of dumping the results of the called SP into a temp table, just execute the called SP. this will get rid of the nesting error, you don't have to create a function and you don't have to use the OPENROWSET call.

 

or am i totally missing something?

 

lenny

Answer 6

I have the same issue, so i am trying your work-around. Basically i want the result set of sp_replmonitorhelpsubscription proc So here is my Openrowset query.

 

SELECT a.*

FROM OPENROWSET('SQLNCLI','LinkedServerName';'MyUser';'Mypassword','exec distribution..sp_replmonitorhelpsubscription @Publisher=''MyPublisher'',@publication_type=0') AS a;

 

When i execute above query, i get this error

 

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 53, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [53].

 

If I use servername instead of linkedserver -- i get this error

 

 

Msg 7355, Level 16, State 1, Line 2

The OLE DB provider "SQLNCLI" for linked server "(null)" supplied inconsistent metadata for a column. The name was changed at execution time.

 

can any one please tell me how do solve this issue?

 

Thanks

 

 

Answer 7

I did check the linked server properties, it is fine. The thing is If i use some select query it works fine. But when i use exec distribution..sp_replmonitorhelpsubscription @Publisher=''MyPublisher'',@publication_type=0 it throws those errors..

I dont know what i am missing..

Answer 8

Yes you are missing something

have u tried properly.

 

When i tried, it gave error displayed below.

 

Server: Msg 197, Level 15, State 1, Procedure InsertIntoSp, Line 20
EXECUTE cannot be used as a source when inserting into a table variable.

 

Answer 9

can you please put an example of how you changed the stored procedures into functions to make this work? openquery is not an option for me.

Answer 10

can you please put an example of how you changed the stored procedures into functions to make this work? openquery is not an option for me.

That is not possible if any kind of database change is involved. You cannot change the state of the database from a function, you can do it from a stored procedure. Also, you cannot use INSERT EXEC within a function.

Following scripts present 3 workarounds: 

           OPENQUERY
           Remove INSERT EXEC from sproc           
           bcp - BULK INSERT

Not pretty, but they do work.

-- SQL Server 2008 T-SQL INSERT-EXEC nesting issue and workarounds
USE tempdb;
GO
-- SELECT INTO create empty tables for testing
SELECT TOP (0) * INTO Alpha
FROM OPENQUERY(DELLSTAR,'EXECUTE [AdventureWorks].[dbo].[uspGetBillOfMaterials] 800, ''2004-02-01''')
GO 
/*
CREATE TABLE [dbo].[Alpha](
	[ProductAssemblyID] [int] NULL,
	[ComponentID] [int] NULL,
	[ComponentDesc] [nvarchar](50) NULL,
	[TotalQuantity] [numeric](38, 2) NULL,
	[StandardCost] [money] NULL,
	[ListPrice] [money] NULL,
	[BOMLevel] [smallint] NULL,
	[RecursionLevel] [int] NULL
) ON [PRIMARY]
*/

CREATE PROC sprocINSERTEXEC 
AS 
  BEGIN 
    SET NoCount ON 
   DECLARE @OMEGA TABLE (
	[ProductAssemblyID] [int] NULL,
	[ComponentID] [int] NULL,
	[ComponentDesc] [nvarchar](50) NULL,
	[TotalQuantity] [numeric](38, 2) NULL,
	[StandardCost] [money] NULL,
	[ListPrice] [money] NULL,
	[BOMLevel] [smallint] NULL,
	[RecursionLevel] [int] NULL)
    INSERT INTO @OMEGA 
    EXECUTE [AdventureWorks].[dbo].[uspGetBillOfMaterials] 800, '2004-02-01'
    SELECT * 
    FROM   @OMEGA; 
  END 

GO 

INSERT INTO Alpha 
EXEC sprocINSERTEXEC 
GO 
/*
Msg 8164, Level 16, State 1, Procedure sprocINSERTEXEC, Line 27
An INSERT EXEC statement cannot be nested.

(0 row(s) affected)
*/

/****** WORKAROUND 1 OPENQUERY ************/

INSERT INTO Alpha
SELECT * FROM OPENQUERY (DELLSTAR, 'EXEC tempdb.dbo.sprocINSERTEXEC')
GO
-- (87 row(s) affected)

/****** WORKAROUND 2 REMOVE INSERT SELECT from sproc *********/
ALTER PROC sprocINSERTEXEC 
AS 
  BEGIN 
    EXECUTE [AdventureWorks].[dbo].[uspGetBillOfMaterials] 800, '2004-02-01'
  END 
GO 

INSERT INTO Alpha 
EXEC sprocINSERTEXEC 
GO 
-- (87 row(s) affected)

/****** WORKAROUND 3 bcp - BULK INSERT round trip to file system ************/

ALTER PROC sprocINSERTEXEC 
AS 
  BEGIN 
    SET NoCount  ON 
   CREATE TABLE #OMEGA  (
	[ProductAssemblyID] [int] NULL,
	[ComponentID] [int] NULL,
	[ComponentDesc] [nvarchar](50) NULL,
	[TotalQuantity] [numeric](38, 2) NULL,
	[StandardCost] [money] NULL,
	[ListPrice] [money] NULL,
	[BOMLevel] [smallint] NULL,
	[RecursionLevel] [int] NULL)
    DECLARE @Command nvarchar(256)
    SET @Command = 'bcp "EXECUTE [AdventureWorks].[dbo].[uspGetBillOfMaterials] 800, ''2004-02-01'' " queryout "f:\temp\bom1.txt" -T -c'
    EXEC xp_cmdshell @Command, NO_OUTPUT
    BULK INSERT #OMEGA
    FROM 'f:\temp\bom1.txt'
    -- (87 row(s) affected)
    SELECT * 
    FROM   #OMEGA; 
  END 
GO 

INSERT INTO Alpha 
EXEC sprocINSERTEXEC 
GO 
-- (87 row(s) affected)

-- Cleanup
DROP TABLE Alpha
DROP PROC sprocINSERTEXEC 

---

Kalman Toth, SQL Server 2008 & BI Training, OLAP, SSIS, SSRS; http://www.SQLUSA.com

Answer 11

Hi,

look this article here from Erland, that´ll help you:

http://www.sommarskog.se/share_data.html

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Good one.