How to fix Error 3915 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 3915 – ROLLBACK Within INSERT-EXEC Statement

Why This Error Happens

Error 3915 occurs because SQL Server does not allow a ROLLBACK TRANSACTION statement to be executed inside a stored procedure or remote query that is itself being called from within an INSERT...EXEC statement. This restriction exists because the INSERT-EXEC construct relies on an open rowset/result-set pipe to stream data back to the calling INSERT, and an uncontrolled rollback mid-stream would corrupt the transaction context and leave the data pipe in an undefined state. This is a design limitation in the SQL Server transaction engine (applicable through SQL Server 2025) rather than a configuration bug, meaning it must be resolved through code refactoring rather than a server-side patch or setting.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Procedure

  • Open SQL Server Management Studio (SSMS) on your Windows Server 2025 or Windows 11 workstation.
  • Run SELECT * FROM sys.dm_exec_query_stats or check the Activity Monitor to locate the exact stored procedure being called via INSERT INTO ... EXEC.
  • Review the procedure definition using sp_helptext 'YourProcedureName' to find internal BEGIN TRAN / ROLLBACK logic.

Step 2: Refactor the Stored Procedure to Remove Internal Rollback

  • Modify the stored procedure so it does not contain a ROLLBACK statement when called in this context.
  • Replace internal transaction handling with TRY...CATCH blocks that use RAISERROR or THROW to pass errors back to the caller instead of rolling back internally.
  • Example pattern:
  BEGIN TRY
      -- Business logic here
  END TRY
  BEGIN CATCH
      THROW; -- Let the calling batch handle rollback
  END CATCH

Step 3: Move the Transaction Control to the Calling Batch

  • Wrap the INSERT...EXEC statement itself in the parent batch with its own transaction handling, rather than relying on the inner procedure.
  • Use the following structure in the calling script:
  BEGIN TRY
      BEGIN TRANSACTION;
          INSERT INTO #TempTable
          EXEC dbo.YourStoredProcedure;
      COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
      IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION;
      THROW;
  END CATCH

Step 4: Use a Table Variable or Temp Table as an Intermediary (If Refactoring is Not Possible)

  • If you cannot modify the source procedure (e.g., it is third-party), avoid using INSERT...EXEC altogether.
  • Switch to OPENROWSET with BULK or OPENQUERY against a loopback linked server to capture the result set without triggering the INSERT-EXEC transactional restriction.
  • Alternatively, configure the procedure to write results directly to a physical or temp table internally, and query that table separately instead of capturing the EXEC output stream.

Step 5: Restart the SQL Server Service to Clear Cached Execution Plans

  • Open Services.msc on Windows Server 2025.
  • Locate SQL Server (MSSQLSERVER) or your named instance.
  • Right-click and select Restart to ensure the old cached execution plan (which may still reference the faulty transaction logic) is cleared.

Alternative or Verification Steps

  • Verify the fix: Re-run the original batch script in SSMS; the INSERT...EXEC call should complete successfully without throwing Error 3915, and check sys.messages to confirm no residual transaction warnings are logged.
  • Alternative workaround: If refactoring is too risky for a production system, use a TRY...CATCH wrapper at the outermost calling script level only, and ensure the inner procedure uses SET XACT_ABORT ON instead of explicit ROLLBACK calls, letting SQL Server handle transaction termination automatically on error.