Ahh…enough shameless self-promotion (for a while), let's talk transactions.
I worked in the past with folks who like to compose transactions. SQL Server supports nesting of transactions and named savepoints but not autonomous transactions. So
CREATE PROCEDURE X
AS
BEGIN TRAN
— work here
COMMIT
calling it standalone means the work is in a transaction. Calling it from procedure Y:
CREATE PROCEDURE Y
AS
BEGIN TRAN
— other work here
EXECUTE X
COMMIT
doesn't start an autonomous transaction, the BEGIN TRAN in X merely ups @@TRANCOUNT by 1. Interesting things happen when you roll back X while its being called by Y.
I'd like to emulate this behavior in SQLCLR, i.e. have a procedure that acts like X, and can be used standalone or composed. I can do something akin to T-SQL (and get the interesting rollback behavior with a slightly different error number) using the BeginTransaction method on the context SqlConnection. I'd heard awhile ago that System.Transactions used inside of SQLCLR would "always do the right thing". AND because of the way promotable transactions work, it would compose a context SqlConnection in the SAME local transaction. A la T-SQL or SqlConnection.BeginTransaction().
It doesn't do this. If I have a SQLCLR proc that looks like this (condensed version):
public static void X {
using (TransactionScope ts = new TransactionScope())
using (SqlConnection conn = new SqlConnection("Context connection=true"))
{
conn.Open();
ts.Complete();
}
}
If SQLCLR X is used standalone, all well and good, local transaction. If SQLCLR X is called from procedure Y (above) then SqlConnection.Open() starts a *distributed* transaction. Apparently it HAS to be this way, at least for now, because of how TransactionScope works.
If you WANT a distributed transaction composed with your outer transaction (your SqlConnection is calling to another instance for example), USE TransactionScope, if you DON'T want one, use SqlConnection.BeginTransaction. It won't act any different from T-SQL (except you do get a different error number) if you roll back inside an inner transaction. But you get a nesting *local* transaction with BeginTransaction.
BTW just is case you wondered if SQLCLR X proc could do this:
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew))
and go for that elusive "autonomous transaction", don't do it. You'll get a message saying "no autonomous transaction. Because SQL Server doesn't support autonomous on a single connection. SQLCLR or not. There is the two connection case, but that's a story for another day.
This was as compressed a blog entry as I could make it, but was still quite long. Any questions?