Erorr logging to a table in a transaction before rollback

Question

Erorr logging to a table in a transaction before rollback

MrAkki

SSCrazy

Points: 2574
More actions
February 12, 2008 at 9:35 am

#182892

Hi,
I have a Client Solution (.net C#) that connects to a database.
Every time a connection is opened a transaction is created....
during the execution of some stored procedures an error may occur.
Now I log this error to a error-log table.
The error (is raised) to the client which rolls back the transaction and shows the exception.
The problem is that the data in the error-log table is also rolled back.
Is there any way to log errors to a table (not the eventlog) and not losing the data after a rollback?
Please help.

Viewing 7 posts - 1 through 6 (of 6 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Log them to a table variable, and in your Catch statement, insert them into the log table AFTER the rollback. Table variables don't do rollback.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

MrAkki SSCrazy Points: 2574 More actions · Answer 2

Thanks for your answer.

The problem is, that the Rollback is initiated by the Client and not in the StoredProcedure....

So I was wondering if I can log the errors to a table outside the transaction.... :unsure:

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

Hi

You will have to catch the error and insert into the table after the transaction is rolled back. you can do this from the application (after roll back) .

"Keep Trying"

MrAkki SSCrazy Points: 2574 More actions · Answer 4

So there is no way to do this in a SP? :unsure:

The Problem is, that I do not have all the info in the client. I wanted to create a XML with some more information and the given Parameters within the SP that causes the error....

Or is there any way to pass a XML to the client during a raiserror ? :unsure:

GSquared SSC Guru Points: 260824 More actions · Answer 5

MrAkki (2/13/2008)
Thanks for your answer.
The problem is, that the Rollback is initiated by the Client and not in the StoredProcedure....
So I was wondering if I can log the errors to a table outside the transaction.... :unsure:

The only way I can think of to do this would be to pass the error data to the client and have it log the error.

Something that might work would be to log the error to a global temp table, initiate another stored procedure that takes it from there and logs it in a permanent table in a separate connection/transaction, and then roll back the main transaction.

The problem is that what you're asking for is a violation of the ACID properties of the database. You want the database to roll back the transaction, but to be in a state different than it was when the transaction started. That's a violation of both Atomic and Consistent.

I'd recommend having the front-end application handle the error, if it's already in charge of the transaction. Better yet is to move the error handling and rollbacks to the proc(s), so the database can manage it's own ACIDity itself.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

MrAkki SSCrazy Points: 2574 More actions · Answer 6

Thanks, for your help.

I get your point. I guess I'm gonna put the error handling completely into the database.

Thanks.

🙂