Error: an insert exec statement cannot be nested – quick tips

Question

The error

INSERT EXEC statement cannot be nested

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

occurred when I execute the Sp1, it will display the error (I have 3 stored procedures Sp1, Sp2 and Sp3. The first (Sp1) will run the second (Sp2) which will save the returned data to @tempTB1 while the second will execute the third (Sp3) as well as save the data in @tempTB2.). I've tried to fix it for some days but have not resolved it yet. Can someone suggest a relevant solution to fix the an insert exec statement cannot be nested issue? Much appreciate your support.

lyytutoria · Accepted Answer

The cause: It looks like you are trying to ‘bubble up’ data from a series of procedures which are stored, but there is a restriction on SQL Server that you can only have one active INSERT-EXEC at a given time. Therefore, this error happens. Solution: To find the way solve this error, you can read this article: How To Share Data Between Stored Procedures. It is a detailed article about patterns that can be used to solve this problem. Another solution I recommend you is converting Sp3 into a Table value function.

Chloë Barre · Answer

This is the only way to do it in SQL Server without having to create a huge convoluted function or execute sql string calls, which would be terrible.

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'setfmtonlyoff' and you can't add dynamic sql inside the openrowset calls, either for the string that contains your stored procedure parameters, or for the table's name. You must use temp tables instead of table variables. It is more efficient in most cases.

Sign Up

Sign In

Forgot Password

ITtutoria Latest Questions

Error: an insert exec statement cannot be nested – quick tips