Ugh. We just learned a nasty truth about SQL Server's transaction handling, so here's some Google-juice for others with a similar problem.

In our code, we were using SqlTransaction objects to maintain a transaction while updating the database. Like this:

 // Open the connection so we can do some stuff in the RowUpdating event fmConn.Open(); try { SqlTransaction txn = fmConn.BeginTransaction(); try { SqlDataAdapter da = GetEntriesAdapter(txn); da.Update(ds, "Entries"); txn.Commit(); } catch { txn.Rollback(); // if the exception wasn't caused by the dataset, throw it // back to the client if (!ds.HasErrors) throw; } } finally { fmConn.Close(); }

Looks pretty straight forward, right? "fmConn" is an SqlConnection object. We create (and begin) a transaction, update the database with an SqlDataAdapter object, and if anything goes wrong we roll back the transaction.

Except it wasn't working. Whenever an error got thrown from the database, we'd get an exception about how the transaction can't be rolled back because it has already been rolled back! WTF? Here's the exception text:

System.Data.SqlClient.SqlException:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Turns out that SQL Server's support for nested transactions is non-intuitive, and I'll explain why.

The stored-procedures we use to update our database are themselves wrapped in BEGIN TRANSACTION/COMMIT TRANSACTION statements. At the top of each of these stored procs there's a line like this:

SET XACT_ABORT ON

XACT_ABORT tells SQL Server to roll back your transaction if something goes awry. However, it turns out that a "ROLLBACK TRANSACTION" will roll back every transaction - not just the innermost one!

The only way to get around this at a database level is to explicitly name your transactions, and roll them back manually using "ROLLBACK TRANSACTION name". This is obviously not an option when you're relying on XACT_ABORT to roll your transaction back for you.

So, as per this Microsoft Knowledgebase entry, the only solution is to wrap your call to SqlTransaction.Rollback() in a try/catch block!

I wonder if SQL Server 2005 addresses this problem!