Getting information out of a transaction that rollsback

I have a client who is logging diagnostic information in the midst of lengthy, nested transactions, and finds that the log informaiton disappears when the outer transaction is rolled back. What he wants to do is essentially defeat the transaction, and get records added to a table from within a transaction that don't participate in the transaction.

I've thought about using an Extended Stored Proc, or an RPC with SET_REMOTE_PROC_TRANSACTIONS set to "OFF", or trying to create a linked server and defeat the distributed transaction system, but before I set out building and testing I'd like to ask the experts how to purposefully get data out of a transaction.

Yes, there are work-arounds possible within the T_SQL Procs, we've talked about loading the debug information into a table var which is passed around until all the transactions are cleared and then is logged, but he doesn't hant to go back and revise hundreds of procs. We're hoping to adjust the single logging function to make a "transaction-proof" INSERT.

Any ideas?

GA Dean

You should be able to write a .NET CLR procedure to do this.

When you create the SqlConnection object in the procedure, do not specify Context Connection=true. Instead, create a "real" connection to the database. Such a connection will not be a part of the ambient transaction.

I do not believe that there is any straight T-SQL way to do this, but since you interested in extended SP's, this technique should make sense to you as well, given MS's recommendation to move away frop extended procs in favor of SQLCLR.

Look up "Regular vs. Context Connections" in Books Online for more information.

|||Thanks, I'll give it a try. Reading the docs it was unclear if we would be able to create a regular connection from a CLR called from within an existing transaction.|||I'm not sure that will work. A context connection runs within the same transaction space as the regular connection. It sounds like you're doing two-phase commits if you have what you called an inner and an outer transaction. An alternative would be to start a different transaction, using a different regular connection, for the logging phase although data from the "production" transaction would have to be explicitly passed to the "logging" transaction. Of course, I'm not sure if that will work either.
|||

GA Dean,

I believe that the easy way to capture logging information into a table variable (you mentioned alreay) while in the transaction.

Thanks.

Naras.

|||A transaction by definition is all or nothing. If it gets rolled back, it never happened. There is no evidence of it, except in the transaction log itself. You cannot do "just part of a transaction". EVERYTHING within the transaction gets rolled back on failure. If you log to another table, that other table will be rolled back also, because it is within the transaction scope.

There is no way to get what you want, except to write a text file with the diagnostic info. You could write out to a file, and then bcp the log file into a table after the transaction is done.