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

by

Troubleshooting Guide: Error 3916 — COMMIT Statement Not Allowed Within INSERT-EXEC Without Explicit Transaction

Why This Error Happens

This error occurs because SQL Server 2025 detects a mismatched transaction nesting level when a stored procedure or dynamic SQL executed inside an INSERT-EXEC statement attempts to issue a COMMIT without a corresponding explicit BEGIN TRANSACTION. SQL Server internally wraps INSERT-EXEC operations in a transaction context to maintain rowset integrity, and any COMMIT inside the called procedure that isn’t balanced by its own BEGIN TRANSACTION breaks this nesting, causing the engine to reject the operation. This is typically a T-SQL coding issue rather than a server misconfiguration, often surfacing after application upgrades, migrations from older SQL Server versions, or when legacy stored procedures assume autocommit behavior.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Stored Procedure or Script

  • Capture the full error text, which includes the procedure name and line number where the COMMIT occurs.
  • Run SELECT * FROM sys.messages WHERE message_id = 3916; in SSMS to confirm the exact error definition for SQL Server 2025.
  • Open the procedure in SSMS using EXEC sp_helptext 'YourProcedureName'; to review its transaction logic.

Step 2: Add Explicit BEGIN TRANSACTION and Matching COMMIT/ROLLBACK

  • Wrap the body of the stored procedure with a properly balanced transaction block:
  • BEGIN TRANSACTION;
  • -- your INSERT/UPDATE/DELETE logic here
  • COMMIT TRANSACTION;
  • Ensure every code path (including error-handling blocks) has a matching COMMIT or ROLLBACK, especially inside TRY...CATCH constructs.
  • Use SET XACT_ABORT ON; at the top of the procedure to ensure transactions are automatically rolled back on error.

Step 3: Refactor INSERT-EXEC Usage

  • Avoid calling procedures with COMMIT statements directly inside an INSERT-EXEC; instead, modify the procedure to return a result set without managing its own transaction when it will be consumed by INSERT-EXEC.
  • If the called procedure must manage its own transactions independently, use OPENROWSET or a table-valued function as an alternative to INSERT-EXEC.
  • Test the modified procedure in isolation using EXEC YourProcedureName; before re-integrating it into the INSERT-EXEC call.

Step 4: Validate Compatibility Level and Configuration

  • Confirm the database compatibility level supports expected transaction behavior by running SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
  • Update to the latest compatibility level for SQL Server 2025 using ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;
  • Restart the SQL Server service via services.msc on Windows Server 2025 if compatibility changes require a service refresh.

Alternative or Verification Steps

  • Re-run the original INSERT-EXEC statement in SSMS; if no Error 3916 appears and the expected rows are inserted, the fix is confirmed.
  • As an alternative, replace INSERT-EXEC with a table variable populated via OPENQUERY or a temporary staging table combined with a MERGE statement to bypass nested transaction constraints entirely.