Errors INSERT EXEC statement cannot be nested and Cannot use the ROLLBACK statement within an INSERT-EXEC statement How to solve this

Question

I have three stored procedures Sp1  Sp2 and Sp3   The first one  Sp1  will execute the second one  Sp2  and save returned data into  tempTB1 and the second one will execute the third one  Sp3  and save data into  tempTB2   If I execute the Sp2 it will work and it will return me all my data from the Sp3  but the problem is in the Sp1  when I execute it it will display this error      INSERT EXEC statement cannot be nested   I tried to change the place of execute Sp2 and it display me another error      Cannot use the ROLLBACK statement   within an INSERT-EXEC statement

User · Answer

I had the same issue and concern over duplicate code in two or more sprocs  I ended up adding an additional attribute for  mode   This allowed common code to exist inside one sproc and the mode directed flow and result set of the sproc

User · Answer

If you are able to use other associated technologies such as C   I suggest using the built in SQL command with Transaction parameter   var sqlCommand   new SqlCommand commandText  null  transaction     I ve created a simple Console App that demonstrates this ability which can be found here  https   github com hecked12 SQL-Transaction-Using-C-Sharp  In short  C  allows you to overcome this limitation where you can inspect the output of each stored procedure and use that output however you like  for example you can feed it to another stored procedure  If the output is ok  you can commit the transaction  otherwise  you can revert the changes using rollback

User · Answer

OK  encouraged by jimhark here is an example of the old single hash table approach  -  CREATE PROCEDURE SP3 as  BEGIN      SELECT 1   Data1      UNION ALL     SELECT 2   Data2   END go   CREATE PROCEDURE SP2 as  BEGIN      if exists  select    from tempdb dbo sysobjects o where o xtype in   U   and o id   object id N tempdb   tmp1            INSERT INTO  tmp1         EXEC SP3     else         EXEC SP3  END go  CREATE PROCEDURE SP1 as  BEGIN      EXEC SP2  END GO      --I want some data back from SP3  -- Just run the SP1  EXEC SP1         --I want some data back from SP3 into a table to do something useful --Try run this - get an error - can t nest Execs  if exists  select    from tempdb dbo sysobjects o where o xtype in   U   and o id   object id N tempdb   tmp1        DROP TABLE  tmp1  CREATE TABLE  tmp1  ID INT  Data VARCHAR 20    INSERT INTO  tmp1 EXEC SP1          --I want some data back from SP3 into a table to do something useful --However  if we run this single hash temp table it is in scope anyway so --no need for the exec insert  if exists  select    from tempdb dbo sysobjects o where o xtype in   U   and o id   object id N tempdb   tmp1        DROP TABLE  tmp1  CREATE TABLE  tmp1  ID INT  Data VARCHAR 20    EXEC SP1  SELECT   FROM  tmp1

User · Answer

I recommend to read this entire article  but here is the most relevant section that addresses your question   Rollback and Error Handling is Difficult In my articles on Error and Transaction Handling in SQL Server  I suggest that  gt  you should always have an error handler like  BEGIN CATCH    IF   trancount  gt  0 ROLLBACK TRANSACTION    EXEC error handler sp    RETURN 55555 END CATCH   The idea is that even if you do not start a transaction in the procedure  you should always include a ROLLBACK  because if you were not able to fulfil your contract  the transaction is not valid  Unfortunately  this does not work well with INSERT-EXEC  If the called procedure executes a ROLLBACK statement  this happens   Msg 3915  Level 16  State 0  Procedure SalesByStore  Line 9 Cannot use the ROLLBACK statement within an INSERT-EXEC statement   The execution of the stored procedure is aborted  If there is no CATCH handler anywhere  the entire batch is aborted  and the transaction is rolled back  If the INSERT-EXEC is inside TRY-CATCH  that CATCH handler will fire  but the transaction is doomed  that is  you must roll it back  The net effect is that the rollback is achieved as requested  but the original error message that triggered the rollback is lost  That may seem like a small thing  but it makes troubleshooting much more difficult  because when you see this error  all you know is that something went wrong  but you don t know what

User · Answer

I encountered this issue when trying to import the results of a Stored Proc into a temp table  and that Stored Proc inserted into a temp table as part of its own operation  The issue being that SQL Server does not allow the same process to write to two different temp tables at the same time   The accepted OPENROWSET answer works fine  but I needed to avoid using any Dynamic SQL or an external OLE provider in my process  so I went a different route   One easy workaround I found was to change the temporary table in my stored procedure to a table variable  It works exactly the same as it did with a temp table  but no longer conflicts with my other temp table insert   Just to head off the comment I know that a few of you are about to write  warning me off Table Variables as performance killers    All I can say to you is that in 2020 it pays dividends not to be afraid of Table Variables  If this was 2008 and my Database was hosted on a server with 16GB RAM and running off 5400RPM HDDs  I might agree with you  But it s 2020 and I have an SSD array as my primary storage and hundreds of gigs of RAM  I could load my entire company s database to a table variable and still have plenty of RAM to spare   Table Variables are back on the menu

User · Answer

I found a work around is to convert one of the prods into a table valued function  I realize that is not always possible  and introduces its own limitations  However  I have been able to always find at least one of the procedures a good candidate for this  I like this solution  because it doesn t introduce any  hacks  to the solution

User · Answer

This trick works for me   You don t have this problem on remote server  because on remote server  the last insert command waits for the result of previous command to execute  It s not the case on same server   Profit that situation for a workaround   If you have the right permission to create a Linked Server  do it  Create the same server as linked server    in SSMS  log into your server  go to  Server Object Right Click on  Linked Servers   then  New Linked Server  on the dialog  give any name of your linked server   eg  THISSERVER server type is  Other data source  Provider   Microsoft OLE DB Provider for SQL server Data source  your IP  it can be also just a dot      because it s localhost Go to the tab  Security  and choose the 3rd one  Be made using the login s current security context  You can edit the server options  3rd tab  if you want Press OK  your linked server is created   now your Sql command in the SP1 is  insert into  myTempTable exec THISSERVER MY DATABASE NAME MY SCHEMA SP2   Believe me  it works even you have dynamic insert in SP2

User · Answer

This is the only  simple  way to do this in SQL Server without some giant convoluted created function or executed sql string call  both of which are terrible solutions    create a temp table openrowset your stored procedure data into it   EXAMPLE   INSERT INTO  YOUR TEMP TABLE SELECT   FROM OPENROWSET   SQLOLEDB   Server  local  TRUSTED CONNECTION YES    set fmtonly off EXEC  ServerName  dbo  StoredProcedureName  1 2 3     Note  You MUST use  set fmtonly off   AND you CANNOT add dynamic sql to this either inside the openrowset call  either for the string containing your stored procedure parameters or for the table name  Thats why you have to use a temp table rather than table variables  which would have been better  as it out performs temp table in most cases

User · Answer

what about just store the output to the static table   Like  -- SubProcedure  subProcedureName --------------------------------- -- Save the value DELETE lastValue subProcedureName INSERT INTO lastValue subProcedureName  Value  SELECT  Value -- Return the value SELECT  Value  -- Procedure -------------------------------------------- -- get last value of subProcedureName SELECT Value FROM lastValue subProcedureName   its not ideal  but its so simple and you don t need to rewrite everything   UPDATE  the previous solution does not work well with parallel queries  async and multiuser accessing  therefore now Iam using temp tables  -- A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished   -- The table can be referenced by any nested stored procedures executed by the stored procedure that created the table   -- The table cannot be referenced by the process that called the stored procedure that created the table  IF OBJECT ID  tempdb   lastValue spGetData   IS NULL CREATE TABLE  lastValue spGetData  Value INT   -- trigger stored procedure with special silent parameter EXEC dbo spGetData 1 --silent mode parameter   nested spGetData stored procedure content  -- Save the output if temporary table exists  IF OBJECT ID  tempdb   lastValue spGetData   IS NOT NULL BEGIN     DELETE  lastValue spGetData     INSERT INTO  lastValue spGetData Value      SELECT Col1 FROM dbo Table1 END   -- stored procedure return  IF  silentMode   0  SELECT Col1 FROM dbo Table1

User · Answer

Declare an output cursor variable to the inner sp     c CURSOR VARYING OUTPUT   Then declare a cursor c to the select you want to return  Then open the cursor  Then set the reference                DECLARE c CURSOR LOCAL FAST FORWARD READ ONLY FOR  SELECT     OPEN c SET  c   c    DO NOT close or reallocate   Now call the inner sp from the outer one supplying a cursor parameter like   exec sp abc a b c    cOUT OUTPUT   Once the inner sp executes  your   cOUT is ready to fetch   Loop and then close and deallocate

User · Answer

My work around for this problem has always been to use the principle that single hash temp tables are in scope to any called procs   So  I have an option switch in the proc parameters  default set to off    If this is switched on  the called proc will insert the results into the temp table created in the calling proc    I think in the past I have taken it a step further and put some code in the called proc to check if the single hash table exists in scope  if it does then insert the code  otherwise return the result set   Seems to work well - best way of passing large data sets between procs

User · Answer

On SQL Server 2008 R2  I had a mismatch in table columns that caused the Rollback error  It went away when I fixed my sqlcmd table variable populated by the insert-exec statement to match that returned by the stored proc  It was missing org code  In a windows cmd file  it loads result of stored procedure and selects it   set SQLTXT  declare  resets as table  org id nvarchar 9   org code char 4     tin char9   old strt dt char 10   strt dt char 10      insert  resets exec rsp reset    select   from  resets   sqlcmd -U user -P pass -d database -S server -Q   SQLTXT   -o  OrgReport txt

User · Answer

This is a common issue when attempting to  bubble  up data from a chain of stored procedures  A restriction in SQL Server is you can only have one INSERT-EXEC active at a time  I recommend looking at How to Share Data Between Stored Procedures which is a very thorough article on patterns to work around this type of problem   For example a work around could be to turn Sp3 into a Table-valued function

[sql-server] Errors: "INSERT EXEC statement cannot be nested." and "Cannot use the ROLLBACK statement within an INSERT-EXEC statement." How to solve this?

Examples related to sql-server

Examples related to sql-server-2008

Examples related to code-reuse