March 4, 2015 at 9:17 am
Guys,
I have a stored procedure that does the following
BEGIN TRANSACTION OUTERTXN
BEGIN TRANSACTION
Copy records from live to archive
END TRANSACTION with commit or rollback
execute sproc to write audit log with success or fail
IF transaction was committed
BEGIN TRANSACTION
Delete records from live the archive
END TRANSACTION with commit or rollback
execute sproc to write audit log with success or fail
End IF
END TRANSACTION OUTERTXN with commit if both inner transactions were successful or rollback if either failed
If either inner transaction rolled back
execute sproc to write audit log saying whole process is rolling back
End If
My problem is that if the outer transaction rolls back then I am losing the two audit records because they are part of the transaction scope. I want these executes to commit even if the master transaction fails.
Can you help?
March 4, 2015 at 9:28 am
It's a feature that people have long asked for, but it's not available directly. In Oracle, this is called "Autonomous transaction" and is used exactly for this kind of tasks (log something to a table and persist it throughout rollbacks, increment sequence tables etc...).
You can work around this limitation with a CLR stored procedure that logs to your table.
Paul White coded a simple CLR stored procedure to simulate sequence tables in versions < 2012. You can find it here: http://sqlblog.com/blogs/paul_white/archive/2010/10/19/sequence-tables.aspx
Other possibilities are log to sql server log or use table variables to store messages (table variables are not affected by transaction rollbacks).
Hope this helps
Gianluca
-- Gianluca Sartori
March 4, 2015 at 9:31 am
Thanks Gianluca, I'll try that now. But you are right, it does seem like a tedious workaround.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply