January 21, 2009 at 3:28 pm
Here's what I'd like to do. I have most SPs coded in TRY CATCH blocks. The CATCH block calls an SP to get @ErrorMessage, etc.
Semi-pseudocode:
BEGIN TRY
BEGIN TRAN
(do some stuff)
END TRY
BEGIN CATCH
(call error SP_Error - populates @Errormessage etc)
END CATCH
IF @ErrorNumber <> 0
IF @@trancount > 0
ROLLBACK TRAN
ELSE
IF @@TRANCOUNT > 0
COMMIT TRAN
I'd like to add the option to log error information inside SP_Error. This is easy. However, if the SP that threw the error rolls back, then the insert into the log table is rolled back as well.
Is there a way to insert data into a table from within a transaction such that that data will not be rolled back?
January 21, 2009 at 3:35 pm
Define a table variable, then insert the error information into the table variables first. After you issue the rollback, then insert the information from the table variable into your error log table.
The table variables are not affected by transactions.
January 22, 2009 at 6:06 am
Of course. I should have thought of that. Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply