// SQL Server error reference

SQL Server error 266: Transaction count mismatch after EXECUTE

A stored procedure or batch ended with a different number of open transactions than it started with: a BEGIN TRANSACTION without COMMIT, or a ROLLBACK too many.

Error number
266
Severity
16
Category
Transactions and locking
Error message
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = %ld, current count = %ld.

What it means

SQL Server counts open transactions in @@TRANCOUNT. When a stored procedure, trigger or RPC call (such as sp_executesql) returns, SQL Server compares the count with the value it had on entry. If they differ, it raises 266 to tell you the module left the transaction state unbalanced.

The error is a warning about the shape of your transaction code. The work inside the procedure has already run; what happens next depends on whether the caller commits or rolls back the transaction that is still open, or never opened one that was rolled back.

Common causes

  • A procedure runs BEGIN TRANSACTION and returns early (an IF ... RETURN, or an error path) without COMMIT or ROLLBACK.
  • A procedure runs ROLLBACK TRANSACTION inside a transaction its caller opened. ROLLBACK always rolls back to the outermost transaction and sets @@TRANCOUNT to 0, so the caller's count no longer matches.
  • A client library or tool sends a BEGIN TRANSACTION through sp_executesql (an RPC call) and expects it to stay open for later statements.
  • SET XACT_ABORT ON rolled the whole transaction back after a runtime error inside a nested procedure.

How to fix it

  1. Check @@TRANCOUNT at the start of the procedure and only open (and later commit) a transaction when there isn't one already. Use a savepoint for nested work, so an error rolls back only the procedure's own changes.

    CREATE OR ALTER PROCEDURE dbo.TransferFunds @From int, @To int, @Amount money
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @OwnTran bit = CASE WHEN @@TRANCOUNT = 0 THEN 1 ELSE 0 END;
        IF @OwnTran = 1 BEGIN TRANSACTION;
        ELSE SAVE TRANSACTION TransferFunds;
        BEGIN TRY
            UPDATE dbo.Accounts SET Balance -= @Amount WHERE AccountId = @From;
            UPDATE dbo.Accounts SET Balance += @Amount WHERE AccountId = @To;
            IF @OwnTran = 1 COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            IF @OwnTran = 1 AND @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
            ELSE IF XACT_STATE() = 1 ROLLBACK TRANSACTION TransferFunds;
            THROW;
        END CATCH
    END;
  2. Make every exit path balance the transaction. Replace bare RETURN statements inside a transaction with a jump to a single exit that commits or rolls back.

  3. When a tool or driver needs a transaction across several statements, open it with the driver's transaction API or send BEGIN TRANSACTION as a plain batch, not as a parameterized sp_executesql call.

  4. If you see 266 together with another error, fix that error first. 266 is often the second message after a failure (for example 1205 or 547) made the procedure roll back.

Reproduce it safely

-- Run on a scratch database.
CREATE OR ALTER PROCEDURE dbo.LeavesTranOpen AS BEGIN TRANSACTION;
GO
EXEC dbo.LeavesTranOpen;   -- Msg 266: Previous count = 0, current count = 1
SELECT @@TRANCOUNT;        -- 1: the transaction is still open
ROLLBACK;
DROP PROCEDURE dbo.LeavesTranOpen;

Questions

Does error 266 roll back my changes?

No. 266 itself does not roll anything back. The changes made inside the procedure are still part of whatever transaction is open; they are kept if the caller commits and undone if it rolls back. Check @@TRANCOUNT right after the call.

Why do I get 266 from my application but not in SSMS?

Many drivers send parameterized statements through sp_executesql, which is an RPC call that SQL Server checks for a balanced transaction count. SSMS sends your text as a plain batch, so a BEGIN TRANSACTION there simply stays open.

Related errors

More transactions and locking errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.