Error 266

Error 266

Severity Level 16

Message Text

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = %ld, current count = %ld.

Explanation

If a stored procedure exits with the @@TRANCOUNT value that is not the same as when the stored procedure was entered, Microsoft® SQL Server™ returns error 266.

Note  This error can be ignored because it only sends a message to the client and does not affect execution.

This example reproduces the problem:

CREATE PROCEDURE test
AS
SELECT @@TRANCOUNT
ROLLBACK TRANSACTION
SELECT @@TRANCOUNT
GO
BEGIN TRANSACTION
EXECUTE test
GO

Because @@TRANCOUNT is not the same in both SELECT statements, error 266 is generated on return from the stored procedure.

This is expected behavior, but it does not mean that transactions cannot be started, completed, or terminated in a stored procedure. Instead, care must be taken so that the @@TRANSACTION function matches on both the entry and exit of the stored procedure. For more information, see ROLLBACK TRANSACTION.

This problem is more likely to occur when writing nested stored procedures.

Action

There is a solution so that the stored procedure works without the error. The following is a list of solutions, with sample code for each:

1. Perform final COMMIT TRANSACTION or ROLLBACK TRANSACTION statements from the same stored procedure nesting level where the transaction began, as shown by the following examples:

   -- Example 1.a
   CREATE PROCEDURE test1a 
   AS
   SELECT @@TRANCOUNT
   GO
   BEGIN TRANSACTION
   EXECUTE test1a
   ROLLBACK TRANSACTION
   GO
   -- Example 1.b
   CREATE PROCEDURE test1c 
   AS
   SELECT @@TRANCOUNT
   GO
   CREATE PROCEDURE test1b 
   AS
   BEGIN TRANSACTION
   EXEC test1c
   COMMIT TRANSACTION
   GO
   EXECUTE test1b
   GO
   

2. If nested transactions are used in a stored procedure, perform matching commits.

   Note  The transaction is not committed until @@TRANCOUNT is equal to 0 (zero).

   -- Example 2
   CREATE PROCEDURE test2b 
   AS
   SELECT @@TRANCOUNT
   BEGIN TRANSACTION
   SELECT @@TRANCOUNT
   COMMIT TRANSACTION
   SELECT @@TRANCOUNT
   GO
   CREATE PROCEDURE test2a 
   AS
   BEGIN TRANSACTION
   EXECUTE test2b
   COMMIT TRANSACTION
   GO
   EXECUTE test2a
   GO
   

3. If a rollback is needed and the stored procedure nesting level is different than where the transaction began, use RAISERROR, with a valid user-defined error, and check the @@ERROR function after the EXECUTE statement.

   -- Example 3
   USE master
   EXECUTE sp_addmessage 50001, 16, 'Rollback of transaction in test3'
   GO
   CREATE PROCEDURE test3 
   AS
   RAISERROR (50001,16,1)
   GO
   BEGIN TRANSACTION
   EXEC test3
       IF @@error <> 50001
           BEGIN
               PRINT 'Commit' 
               COMMIT TRANSACTION 
           END 
       ELSE 
           BEGIN 
               PRINT 'Rollback' 
               ROLLBACK TRANSACTION 
           END
   GO 
   

4. The exception to this rule is that if a trigger performs a rollback, @@TRANCOUNT need not match its starting value, because the batch is terminated. However, a stored procedure called by a trigger may cause the problem if it terminated the transaction.

   -- Example 4
   CREATE TABLE x (col1 int)
   GO
   CREATE TRIGGER xins 
       ON x 
       FOR INSERT AS
           ROLLBACK TRANSACTION
   GO
   CREATE PROCEDURE sp_xinsert 
   AS
       SELECT @@TRANCOUNT
       INSERT x (col1) VALUES (1)
       SELECT @@TRANCOUNT
   GO
   BEGIN TRANSACTION
   EXECUTE sp_xinsert
       IF @@error <> 0
           BEGIN
               PRINT 'Commit' 
               COMMIT TRANSACTION 
           END 
       ELSE 
           BEGIN 
               PRINT 'Rollback' 
               ROLLBACK TRANSACTION 
           END 
   GO 
   SELECT * 
   FROM x
   

See Also

@@ERROR

@@TRANCOUNT

BEGIN TRANSACTION

COMMIT TRANSACTION

Errors 1 - 999

EXECUTE

ROLLBACK TRANSACTION

Transactions