Errors: "INSERT EXEC statement cannot be nested." and "Cannot use the ROLLBACK statement in an INSERT-EXEC statement." How to solve this?

Question

I have three stored procedures Sp1 , Sp2 and Sp3 .

The first ( Sp1 ) will execute the second ( Sp2 ) and save the returned data in @tempTB1 , and the second will execute the third ( Sp3 ) and save the data in @tempTB2 .

If I started Sp2 , it will work, and it will return all my data from Sp3 to me, but the problem is in Sp1 , when I execute it, it will display this error:

INSERT EXEC statement cannot be nested

I tried changing the place of execute Sp2 and displayed another error:

Cannot use the ROLLBACK statement in an INSERT-EXEC statement.

Answer 1

This is a common problem when trying to “pull” data out of a chain of stored procedures. A limitation on SQL Server can be only one INSERT-EXEC at a time. I recommend a look at How to share data between stored procedures , which is a very thorough article on templates to solve this problem.

For example, the job might be to turn Sp3 into a table function.

Answer 2

This is the only “easy” way to do this in SQL Server without any gigantic confusing function created or sql string call, both of which are terrible solutions:

• create temporary table
• opens stored procedure data in 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 an openrowset call, or for a string containing stored procedure parameters, or for a table name. This is why you should use the temp table rather than table variables, which would be better since in most cases it executes a temporary table.

Answer 3

I found that the job is to convert one of the prods to a table-valued function. I understand that this is not always possible, and introduces its own limitations. Nevertheless, I managed to find at least one of the procedures suitable for this. I like this solution because it does not introduce any “hacks” to the solution.

Answer 4

My job for this problem has always been to use the principle that single temp hash tables are in scope for any called processes. Thus, I have an option in the proc parameters (the default value is off). If this is enabled, the called proc will insert the results into the temp table created in the calling proc. I think in the past I took another step and put some code in the called proc to check if there is a single hash table in scope if it inserts the code, otherwise return the result set. Seems to work well - the best way to transfer large datasets between procs.

Answer 5

OK, inspired by jimhark, an example of an old hash table syntax is given: -

 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 */ 

Answer 6

This trick works for me.

You do not have this problem on the remote server, because on the remote server the last insert command is waiting for the previous command to complete. This is not the case on the same server.

Bring this situation for a workaround.

If you have the right to create a Linked Server, do it. Create the same server as the linked server.

• in SSMS, log in to your server
• go to server object
• Right click on “Linked Servers”, then “New Linked Server”
• in the dialog box, specify any name for your linked server: for example: THISSERVER
• Server Type - "Other Data Source"
• Supplier: Microsoft OLE DB Provider for SQL Server
• Data source: your IP address, it could just be a period (.), Because it is localhost
• Go to the "Security" tab and select the third option "Be made using the current login security context"
• You can change the server settings (3rd tab) if you want
• Click OK, your linked server is created.

now your Sql team in Service Pack 1

 insert into @myTempTable exec THISSERVER.MY_DATABASE_NAME.MY_SCHEMA.SP2 

Believe me, it works even with dynamic insertion in SP2

Answer 7

I had the same problem and the problem with duplicate code in two or more sprocs. In the end, I added an additional attribute for "mode". This allowed us to use common code inside one sproc and directed flow and the result of the sproc set.

Answer 8

how easy is it to save the output to a static table? how

 -- 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 perfect, but its so simple and you don’t need to rewrite everything.

UPDATE : the previous solution does not work with concurrent queries (asynchronous and multi-user access), so now I use temporary 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 contents

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

Answer 9

Declare an output cursor variable for internal sp:

 @c CURSOR VARYING OUTPUT 

Then declare cursor c to select which you want to return. Then open the cursor. Then set the link:

 DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT ... OPEN c SET @c = c 

DO NOT close or redistribute.

Now call the inner sp from the outer, specifying the cursor parameter, for example:

 exec sp_abc a,b,c,, @cOUT OUTPUT 

After doing the inner sp, your @cOUT ready to be retrieved. Loop and then close and release.

Answer 10

In SQL Server 2008 R2, I had a mismatch in the table columns that caused the Rollback error. It left when I fixed the sqlcmd table variable populated by the insert-exec statement to match the return of the stored procedure. Missing org_code. In the cmd file, Windows loads the result of the 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"