Microsoft KB Archive/68938

= INF: @@TRANCOUNT Global Variable Detects Rollback =

Article ID: 68938

Article Last Modified on 11/25/2003

-

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 7.0 Standard Edition

-



This article was previously published under Q68938



SUMMARY
@@TRANCOUNT is a global variable that contains the number of currently active transactions for the current user connection. This global variable can be used in the middle of a BEGIN TRANSACTION ... COMMIT TRANSACTION group to determine when and if that transaction has been rolled back.

The @@TRANCOUNT variable is incremented each time a new transaction is started with a BEGIN TRANSACTION command. It is decremented when a COMMIT TRANSACTION command is executed, or when the transaction has been rolled back. This global variable can be accessed by using the SELECT @@TRANCOUNT command.

When using only one transaction, @@TRANCOUNT will be 0 (zero) before the transaction. After the BEGIN TRANSACTION command is executed, @@TRANCOUNT will be 1 (one). If the transaction is completed successfully, @@TRANCOUNT will remain 1 until a COMMIT TRANSACTION command is performed. After this, @@TRANCOUNT will again be 0. However, if an error is encountered that causes the entire transaction to be rolled back, @@TRANCOUNT will be set to 0. This will occur before the COMMIT TRANSACTION command is executed.

This is especially useful when executing each statement of the transaction separately. Some errors will cause the transaction to be rolled back, while others will not. When you receive an error message, a quick check of @@TRANCOUNT will tell you whether your current transaction is still valid, or if it has been automatically rolled back.



MORE INFORMATION
Please note that when writing a DB-Library (DB-Lib) program, @@TRANCOUNT is global to one dbproc. If one DB-Library program opens two dbprocs to access the same SQL Server, each dbproc will have its own local, independent @@TRANCOUNT variable.

Additional query words: Windows NT

Keywords: kbprogramming KB68938

-

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

© Microsoft Corporation. All rights reserved.