\nAhh…enough shameless self-promotion (for a while), let's talk transactions.\n<\/p>\n
\nI 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\n<\/p>\n
\nCREATE PROCEDURE X
\nAS
\nBEGIN TRAN
\n— work here
\nCOMMIT\n<\/p>\n
\ncalling it standalone means the work is in a transaction. Calling it from procedure Y:\n<\/p>\n
\nCREATE PROCEDURE Y
\nAS
\nBEGIN TRAN
\n— other work here
\nEXECUTE X
\nCOMMIT\n<\/p>\n
\ndoesn'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.\n<\/p>\n
\nI'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().\n<\/p>\n
\nIt doesn't do this. If I have a SQLCLR proc that looks like this (condensed version):\n<\/p>\n
\npublic static void X {
\nusing (TransactionScope ts = new TransactionScope())
\nusing (SqlConnection conn = new SqlConnection("Context connection=true"))
\n{
\n conn.Open();
\n ts.Complete();
\n}
\n}\n<\/p>\n
\nIf 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.\n<\/p>\n
\nIf 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.\n<\/p>\n
\nBTW just is case you wondered if SQLCLR X proc could do this:\n<\/p>\n
\nusing (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew))\n<\/p>\n
\nand 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.\n<\/p>\n
\nThis was as compressed a blog entry as I could make it, but was still quite long. Any questions?<\/p>\n","protected":false},"excerpt":{"rendered":"
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. […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,38],"tags":[],"class_list":["post-917","post","type-post","status-publish","format-standard","hentry","category-data-access","category-sqlclr"],"yoast_head":"\n