INF: Stored Procedures, Transactions, and Error 266

ID: Q158325

The information in this article applies to:

Microsoft SQL Server versions 4.2x, 6.0, 6.5

SUMMARY

If a stored procedure exits with the @@trancount value that is not the same as when the stored procedure was entered, the following error will occur:

Error: 266, Severity: 16, State: 1
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN
is missing. Previous count = %ld, Current count = %ld.

This error is for informational purposes only.

MORE INFORMATION

The following is a short code example that demonstrates the problem:


   CREATE PROCEDURE test AS
   SELECT @@trancount
   ROLLBACK TRANSACTION
   SELECT @@trancount
   GO
   BEGIN TRANSACTION
   EXEC 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 aborted in a stored procedure. Instead, care must be taken so that the @@trancount global variable matches on both entry and exit of the stored procedure. This is documented in Transact-SQL under the topic Reference\T\Transactions\Rollback Transaction.

This problem is more likely to occur when writing nested stored procedures. However, there is solution so that the stored procedure works without the error. The following is a list of solutions, with sample code for each:

Perform final commit or rollback transactions 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
      EXEC 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
      EXEC test1b
      GO

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
      EXEC test2b
      COMMIT TRANSACTION
      GO
      EXEC test2a
      GO

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 global variable after the EXECUTE command.


      -- Example 3
      USE master
      EXEC 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

The exception to this rule is that if a trigger performs a rollback, @@trancount needs not match its starting value, because the batch is aborted. However, a stored procedure called by a trigger may cause the problem if it aborted 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
      EXEC sp_xinsert
      IF @@error <> 0
      BEGIN
         PRINT 'Commit'
         COMMIT TRANSACTION
      END
      ELSE
      BEGIN
         PRINT 'Rollback'
         ROLLBACK TRANSACTION
      END
      GO
      SELECT * FROM x

Additional query words: commit rollback trigger

Keywords : kbprg SSrvProg SSrvStProc SSrvTran_SQL
Version : 4.2x 6.0 6.5
Platform : WINDOWS
Issue type :