How to create an autonomous transaction in SQL Server 2008

» How to create an autonomous transaction in SQL Server 2008 | SQL Server Feeds

Sun, 24 Aug 2008 04:00:41 GMT

PingBack from http://www.sqlserverfeeds.com/969/how-to-create-an-autonomous-transaction-in-sql-server-2008-2/

Emulating autonomous transactions in MS SQL Server

Mil's unrated ramblings — Fri, 12 Dec 2008 16:25:26 GMT

Emulating autonomous transactions in MS SQL Server

re: How to create an autonomous transaction in SQL Server 2008

msolcia — Thu, 05 Mar 2009 16:37:45 GMT

Good afternoon,

I'm very interested to the SQLCLR sample and script you mentioned in the post.

Currently for logging purpose inside a Stored Procedure, I'm using the 2nd option ("Using table variable to save data within transaction."). A disadvantage of this solution is that I can dump the table-variable only after the end of the transaction. This doesn't allow to use the log as a trace of the execution (answer to the question: at which point of SP the execution is?)

Thanks for your posts

re: How to create an autonomous transaction in SQL Server 2008

tomponent — Fri, 21 Aug 2009 21:55:48 GMT

I think you could simply use SAVE TRANSACTION intelligently without having to worry about trying to enlist a transaction in a distributed transaction as in:

BEGIN TRAN OUTER

BEGIN TRAN INNER1

SAVE TRAN INNER1

--code

-- choose to commit/ rollback inner1 or not

BEGIN TRAN INNER2

SAVE TRAN INNER2

-- code

-- choose to commit/rollback inner2 or not

COMMIT OUTER

re: How to create an autonomous transaction in SQL Server 2008

ab5sr — Fri, 21 Aug 2009 22:10:59 GMT

Well, that might work, Tom, but I still contend that his placement of the ROLLBACK TRAN OuterTran is in the wrong place; putting it along with the COMMIT TRAN OuterTran with a check for the error works perfectly for me in this instance...

Lee Everest