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
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 TRANSACTIONand returns early (anIF ... RETURN, or an error path) withoutCOMMITorROLLBACK. - A procedure runs
ROLLBACK TRANSACTIONinside a transaction its caller opened.ROLLBACKalways rolls back to the outermost transaction and sets@@TRANCOUNTto 0, so the caller's count no longer matches. - A client library or tool sends a
BEGIN TRANSACTIONthroughsp_executesql(an RPC call) and expects it to stay open for later statements. SET XACT_ABORT ONrolled the whole transaction back after a runtime error inside a nested procedure.
How to fix it
-
Check
@@TRANCOUNTat 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; -
Make every exit path balance the transaction. Replace bare
RETURNstatements inside a transaction with a jump to a single exit that commits or rolls back. -
When a tool or driver needs a transaction across several statements, open it with the driver's transaction API or send
BEGIN TRANSACTIONas a plain batch, not as a parameterizedsp_executesqlcall. -
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
3902COMMIT has no corresponding BEGIN3903ROLLBACK has no corresponding BEGIN3930Transaction cannot be committed1205Deadlocked and chosen as the victim3609Transaction ended in the trigger
More transactions and locking errors
1222Lock request time out period exceeded3960Snapshot isolation update conflict3616Error raised during trigger execution6401No transaction or savepoint found3998Uncommittable transaction rolled back3621The statement has been terminated
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.
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.