October 19, 2009 at 5:52 am
I am trying to write some log entries to a custom table, every time I catch an error in my T-SQL or stored procedures. The problem is that after logging the error, I want to raise the error again to make the calling application handle the error. The application in turn rolls back the transaction (As it is supposed to, when it receives an error) - But rolling back the transaction also rolls back the log entries I'm interested in keeping.
In .NET it is possible to create a suppressed transaction scope, which allows the execution of T-SQL that is not part of the existing transaction - but I am not able to find any similar functionality in T-SQL.
Anyone have a good idea on how I can maintain my log entries, even though the transaction has rolled back ?
October 19, 2009 at 6:45 am
Simply move your logging statement out of the transaction.
BEGIN TRANSACTION
BEGIN TRY
-- Do something here
-- ...
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
INSERT INTO LogTable VALUES ('Error occured')
END CATCH
-- Gianluca Sartori
October 19, 2009 at 7:54 am
You have to move the log writing to the place that handles the errors. If it is the application, then you’ll have to use the application’s code to write information to the log. Another alternative is to move the error handling (and the transaction handling) to the stored procedure and do it from the procedure (as Gianluca Sartori already wrote).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 19, 2009 at 8:06 am
Mr. Andersen (10/19/2009)
Anyone have a good idea on how I can maintain my log entries, even though the transaction has rolled back ?
Log to a table variable, they're unaffected by rollbacks. Right at the end (after commit/rollback) you can insert the log entries from there into the real table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2009 at 8:13 am
I was going to write what Gail did. I like the idea of persisting these in a table variable and then writing them out. I'd still use TRY CATCH, but you might want to log some things in the TRY as well without duplicating code.
October 19, 2009 at 8:13 am
Great idea, Gail.
I never have thought about that!
The main point, however, is that the piece of code (stored procedure, application code etc...) that requested the transaction to start is responsible for commit/rollback. I would not place logging statements in a place other than that. From inner procedure I would simply raise the error, so that the caller can catch it and handle it in any way it finds appropriate.
-- Gianluca Sartori
October 20, 2009 at 1:16 am
GilaMonster (10/19/2009)
Log to a table variable, they're unaffected by rollbacks. Right at the end (after commit/rollback) you can insert the log entries from there into the real table.
That's a really good idea, I'll try to use this.
Thank you !
December 7, 2009 at 7:59 pm
I'm with Gila on that. I have done that many times precisely because it is not part of the transaction. You just have to remember to write it out at the end..
CEWII
May 25, 2023 at 8:24 pm
Without using code inside Aplication, one way: Use other external resource as MySQL p exemple in the same machine's SQL Server, create your table log inside MySQL; On your database from SQLServer create a new LinkedServer to connect MySQL, so the MySQL turns one "extension" of your SQL Server; on the trigger of SQL Server do your log INSERT to your LinkedServer, so this way if rollback occurs will do not affect record inserted on LinkeServer MySQL. You dont need any code on your application, the SQLServer will do the log on MySQL. You need attention because could be bad decision to do this, consider only on necessary situations and do tests to see if work well.
One other way can be using temporary tables inside trigger, that don't participe of ACID transacion, so you can build some strategy to colect the records from your temp table after end of transaction not importing if was COMMIT or ROLLBACK.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply