Microsoft KB Archive/158325

= INF: Stored Procedures, Transactions, and Error 266 =

Article ID: 158325

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q158325



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: kbinfo kbprogramming KB158325

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.