none
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

    Wednesday, April 12, 2006 1:51 PM

Answers

  • 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
    ---

    Wednesday, April 12, 2006 1:59 PM
    Moderator
  • 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 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Saturday, January 30, 2010 1:56 PM
    Moderator

All replies

  • 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
    ---

    Wednesday, April 12, 2006 1:59 PM
    Moderator
  • 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

    Monday, July 30, 2007 10:13 AM
  • 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

     

     

    Monday, July 30, 2007 10:35 AM
  • 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

    Thursday, March 13, 2008 12:33 PM
  • 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

    Wednesday, July 02, 2008 3:12 PM
  • 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

     

     

    Monday, July 21, 2008 7:29 PM
  • 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..

    Tuesday, July 22, 2008 2:39 PM
  • 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.

     

    Thursday, October 23, 2008 9:06 AM
  • 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.
    Saturday, January 30, 2010 7:22 AM
  • 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 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Saturday, January 30, 2010 1:56 PM
    Moderator
  • 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.
    Friday, May 28, 2010 5:49 AM
  • Não encontrei a solução no link informado, podem apontar por favor.

    Estou com esse problema.

    Precisei fazer uma procedure que trabalha com duas tabelas temporárias e depois faço uma pivot table.

    Após isso dá merda se chamo o record set.

    limitação do asp e sql, uma porcaria !

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

    Friday, April 01, 2016 8:42 PM
  • Insert Into #MyScriptTable

    SELECT a.*

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

    Its worked for me

    This one is perfect solution where we use to insert data into table by procedure and that procedure is called by Function.

    Thanks a lot man...

    Wednesday, July 05, 2017 8:50 AM