December 3, 2008 at 8:57 am
I log activity to one table in my database. In the case of nested stored procedures, when an error occurs, my error log entries are rolled back.
How can I always commit inserts to this one table, even when the overall transaction chain is rolled back? I use a stored procedure to insert messages into my log.
Thanks!
December 3, 2008 at 9:28 am
Insert the log data into a table variable. Then, in your error handling, after the rollbacks, insert it into the final table. That should give you what you need.
- 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
December 3, 2008 at 11:45 am
GSquared (12/3/2008)
Insert the log data into a table variable. Then, in your error handling, after the rollbacks, insert it into the final table. That should give you what you need.
To add, that's because Table Variable doesn't participate in Transactions (temp tables do)
so values in there will remain even a ROLLBACK occurs
December 3, 2008 at 12:27 pm
The problem I face using a table variable is that I may have 10 or more log entires in a series of nested procedures leading up to the point of failure. It's actually these log entries I really want to preserve, because they provide some valuable clues as to why the failure occurred.
I was hoping that there was some magic bullet I could easily implement without having to take the low-tech route of writing to a text file.
If there are any other ideas, I would appreciate them.
Thanks.
December 4, 2008 at 11:48 am
Have the first proc create a temp table. As each proc is called, insert your logging data into that same temp table (assuming they are all called in the same scope). Before you do a rollback, insert from the temp table into a table variable. Then do a rollback, then insert from the table variable into a permanent table.
It's a little indirect, and only works if it's multiple procs calling each other (same connection, same scope), as opposed to procs being called from some outside piece of code, but it should get the job done.
A lot of how well something like that will work depends on how you've set up your Try-Catch blocks, or other error handling.
- 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
December 4, 2008 at 2:07 pm
GSquared - Thanks for the idea. I should be able to easily work that into my logging sproc. I will let you know how that works out by Monday.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply