Forcing Exception logging not be SUBJECT to ROLLBACK inside a transaction

Question

Forcing Exception logging not be SUBJECT to ROLLBACK inside a transaction

Dean Natenzon

Valued Member

Points: 62
More actions
April 14, 2004 at 3:59 pm

#108123

I need to be able to log (insert) data exceptions into an exception table
from stored procedures and triggers.
I wrote a small stored procedure to perform data exception logging that is now being envoked by other SPs and Triggers.
A problem arises with the logged exception event when my Exception SP is used from inside a BEGIN TRANSACTION/End TRANSACTION sequence.
If a transaction is ROLLED back, by exception entires are ROLLED BACK as well, rightfully so.
Are there any methods at the table level to prevent rollbacks from taking place ?
Can any other strategies be recommended for retaining certain inserts even though an overall ROLLBACK was performed ?
Any suggestions would be much appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mssql_rules SSCrazy Points: 2783 More actions · Answer 1

When the outer transaction is rolled back does not it roll back all modifications, irrespective of any commits by the inner transactions? It makes no difference whether you have transactions in the procedure. (as shown below)

create table logger (logentry varchar (30))

go

create proc Testproc @entry varchar(30) as

begin tran

insert into logger values (@entry)

commit tran

go

begin tran

insert into logger values ('Outer')

exec Testproc 'Inner'

select * from logger

Output

======

logentry

------------------------------

Outer

Inner

(2 row(s) affected)

rollback tran

select * from logger

logentry

------------------------------

(0 row(s) affected)

lakusha SSCommitted Points: 1857 More actions · Answer 2

If you don't mind some overhead, make an external call that can't be enrolled in the transaction. Some examples that come to mind are:

- a DTS package

- a direct call to osql.exe with xp_cmdshell

- log in the NT application log instead with xp_logevent

- use a COM and sp_OA procedures

etc.

I log serious, i.e. unexpected, errors with xp_logevent. I log application logic errors in the db but I handle these before opening the transaction (so there is usually no reason to rollback).

Eric

Dean Natenzon Valued Member Points: 62 More actions · Answer 3

Thank you all contributers for your replies.

Eric, I will try the external osql.exe call. This is nice and simple !!

Many thanks again !