T-SQL: How to force rows insertion ignoring transactions?

[Yuval Naveh]

Hi, I have a logger written in T-SQL (Stored Procs) writing to a log table. When there are exceptions a log row is written, but then when the outer transaction rolls back it also rolls back the log entry. I would like to have the log entry (i.e. row) to be inserted and persisted regardless of what the outer (nested) transactions are doing. The log entry should never roll back Could someone point me to a solution using T-SQL? Thanks Yuval

"The true sign of intelligence is not knowledge but imagination." - Albert Einstein

[Lost User]

Yuval Naveh wrote:

I would like to have the log entry (i.e. row) to be inserted and persisted regardless of what the outer (nested) transactions are doing. The log entry should never roll back Could someone point me to a solution using T-SQL?

You could use a raiserror[^] to write your message to the sys.messages view. You'd have to predefine the errormessages using sp_addmessage though. Alternatively, if it's from a stored procedure then you might choose to add them to a variable, as opposed to logging them immediately. Next, RETURN that variable when exiting the sproc, and write insert it's contents to the logfile loggingtable. If you have the option to drop the TSQL-requirement, then go for extended procedures, like xp_cmdshell (executing isql.exe to execute an SQL statement). I hope someone else comes up with something better :)

I are Troll :suss:

modified on Tuesday, January 19, 2010 7:31 PM

[debrah h48]

Check out quires here. http://technet.microsoft.com/en-us/library/ms187373.aspx[^]

[Yuval Naveh]

Hi Eddy, Using the xp_cmdshell to come back and insert rows is a possibility - looks a bit cumbersome though. If I can't figure it out a more elegant way this might be a (temporary) solution. Thanks, Yuval

"The true sign of intelligence is not knowledge but imagination." - Albert Einstein

[Yuval Naveh]

Hi, I could not find a specific hint that solves the problem. Thanks Yuval

"The true sign of intelligence is not knowledge but imagination." - Albert Einstein