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 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 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