StaticGround

SQL Server 2000 does not support nested transactions. So, if we have the following two stored procedures:

SP1:

create procedure sp1
as

begin tran
exec sp2

if (@@error <> 0) 
  goto on_error

commit tran

on_error:
rollback tran
 


SP1:
 create procedure sp2
 as
 
 begin tran
insert table values ('sp2')
 
 if (@@error <> 0) 
   goto on_error
 
 commit tran
 
 on_error:
 rollback tran

The rules for nested begin transactions is:

• Any begin tran statements are ignored, but each begin tran statement does update the @@trancount variable.
  
• Any commit statements executed after the first commit transaction statement are ignored. The transaction is committed only when the outermost commit statement is executed. The @@trancount variable is decremented by one for each commit statement executed.
  
• Any rollback statements executed will rollback the entire transaction and set the @@trancount variable to 0.
  

Basically, I think of nested transactions as really just one main transaction that could contain multiple time points where the state of the transaction (committed, rollback) can be modified.

When using the ADO.NET SqlTransaction object you need to be aware of how transactions are committed and rolled back. When the SqlTransaction.BeginTransaction method is executed, it implicitly executes the BEGIN TRAN SQL statement. Any SQL statements executed will then execute on this main transaction. If the SQL statements that are executed perform a commit or rollback, the main transaction is affected based on the rules above. There may be certain cases where a servere SQL error will cause the main transaction to rollback, and if the main transaction is created by the SqlTransaction.BeginTransaction method, when the exception is caught, you will need to check if the transacion was actually already rolled back before executing the SqlTransaction.Rollback method:

try
{
    try
    {
       SqlTransaction txn = new SqlTransaction();

       txn.BeginTransaction();

       Data.CallSproc1();
       Data.CallSproc2();  // throws an exception and rolls back the transaction
       txn.CommitTransaction();
    }
    catch(SqlException ex)
    {
        txn.RollbackTransaction();  // If the transaction is already rolled back, this will throw an exception
    }
}
catch(SqlException ex)
{
    // Transaction was already rolled back, do something
}
 }

If you want a good description of NTLM Authentication (Windows authentication), you can find a good resource here