Error 266

Severity

10

Message text

Transaction count after EXECUTE indicates that  a COMMIT or ROLLBACK TRAN is missing. Previous count = %ld,  Current count = %ld.

Explanation

When a stored procedure runs, Adaptive Server maintains a count of open transactions, adding 1 to the count when a transaction begins, and subtracting 1 when a transaction commits. When you execute a stored procedure, Adaptive Server expects the transaction count to be the same before and after the stored procedure execution. Error 266 occurs when the transaction count is different after execution of a stored procedure than it was when the stored procedure began.

Error 266 occurs most often when stored procedures are executed in chained mode. In chained mode, if there is no explicit begin transaction statement, an implicit begin transaction is issued. A begin transaction statement, whether implicit or explicit, increments the session transaction count by 1.

In chained mode, the following statements cause an implicit begin transaction to be issued: select, update, delete, insert, and fetch. (The fetch command begins a transaction only when close on endtran is off in chained mode and cursors can remain open across transactions. See the Reference Manual: Commands for a discussion of the close on endtran option of the set command.)

Each begin transaction statement in a stored procedure, whether implicit or explicit, must be balanced by a commit transaction statement, which decrements the session transaction count by 1. Otherwise, the transaction count remains higher when the stored procedure exits than it was at the beginning of execution.

In addition, error 266 occurs when you are using nested procedures, and procedures at each level of nesting include begin, commit, and rollback transaction statements. If a procedure at a lower nest level opens a transaction and one of the called procedures issues a rollback transaction, error 266 occurs when you exit the nested procedure.

For example:

1> create procedure <proc1>
2> as
3> begin transaction
   .
   .
   execute <proc2>
4> commit transaction
5> go

1> create procedure <proc2>
2> as
3> begin transaction
   .
   .
4> rollback/commit transaction
5> go

If <proc2> executes a rollback transaction, error 266 is raised.

Action

In this example, execution of the stored procedure “test” results in error 266:

1> use pubs2 
2> go

1> create procedure test as 
2> select * from titles 
3> go 

1> sp_procxmode test, chained 
2> go 

1> set chained on 
2> go

1> execute test 
2> go

The transaction count is zero (0) before executing stored procedure “test”. When the stored procedure is executed, the select statement causes the transaction to begin implicitly. Since there is no balancing commit transaction for the implicit begin transaction the transaction count is not decremented in the stored procedure. The stored procedure returns to the caller with a transaction count of 1. A nonfatal 266 error is raised.

To balance the implicit begin transaction within a stored procedure, issue a commit statement before exiting the stored procedure. For example:

1> use pubs2 
2> go 

1> create procedure test as 
2> select * from titles 
3> commit transaction 
4> go 

1> sp_procxmode test, chained 
2> go 

1> set chained on 
2> go

1> execute test 
2> go

The transaction count is zero (0) before stored procedure execution. When the stored procedure is executed, the select results in an implicit begin transaction and the transaction count is incremented to one. The commit transaction in the stored procedure decrements the transaction count to zero (0). The transaction count is the same at the end of stored procedure execution as it was in the beginning.

You can use the @@trancount global variable to check the transaction count at the beginning and end of the procedure. If the count is not equal, commit or roll back transactions as appropriate.

Additional information

For more information on transactions within stored procedures and the @@trancount global variable, see “Checking the transaction nesting level with @@trancount” in the “Global Variables” section of the Transact-SQL User's Guide.

Versions in which this error is raised

All versions