VistaDB 5
Number of open transactions

VistaDB only supports a single open transaction (no nested transactions are supported). It is therefore very useful to be able to determine how many open transactions are present prior to creating one, or in an error handling loop.

Open Transactions in DDA

Given a VistaDB Database like this( IVistaDBDatabase db ) you can query the NestedTransactionLevel property to determine how many transactions are open.

Open Transaction Count in DDA
Copy Code
        
   if( db.NestedTransactionLevel > 0 )
   {
      db.RollbackTransaction();
   } 

Open Transactions in SQL

In a SQL Proc you may want to check for transactions using the @@TRANCOUNT system variable.

The query will return 0 for none, or a positive number (only 1 is valid at this time). If no database is open or if the database is in an invalid state -1 will be returned to represent an error condition.

Open Transaction Count in SQL
Copy Code
SELECT @@TRANCOUNT; 

A stored procedure capable of handling an exception in a TSQL Proc that contains transactions should also check the TRANCOUNT and rollback if it is found to be greater than 0.

SQL Stored Procedure Example
Copy Code
BEGIN TRY
     { sql_statement | statement_block }
END TRY
BEGIN CATCH
-- Do we have a transaction to rollback?
IF @@TRANCOUNT > 0
BEGIN
          ROLLBACK TRANSACTION;
END
END CATCH 
See Also