2

I have a (SQL 2005) stored procedure that processes a giant table and groups old data from the past (up til one year ago). It has this main steps:

  • copy old data grouped to a new table
  • copy recent data as is to the new table
  • rename table

Now I want to log every run and every step in logging tables. However I start a transaction in the beginning so that I can rollback the whole batch if something goes wrong. But that would also rollback my logging which isn't what I want.

How can I resolve this?

Koen
  • 3,626
  • 1
  • 34
  • 55

2 Answers2

1

Log to a table variable as this doesn't get rolled back with the transaction then at the end of the procedure after commit or rollback insert the contents of the table variable into your permanent logging table.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Would bring be some steps further but aren't there fatal errors that can shut down the whole procedure which gives me no control on persisting the log table variable? – Koen Jan 19 '12 at 13:51
  • @Koen - If you need to cater for that (e.g high severity errors that terminate the connection) only thing I can suggest is logging to the event log or a trace instead (with [user configurable SQL Server Profiler events](http://weblogs.sqlteam.com/mladenp/archive/2008/10/16/Custom-user-configurable-SQL-Server-Profiler-events.aspx)) – Martin Smith Jan 19 '12 at 13:56
1
  • Use SET XACT_ABORT ON to force rollback
  • To catch all errors (where code runs), use TRY/CATCH blocks.

Then, you can simply log the errors in your CATCH blocks.

Example here (can add your own logging): Nested stored procedures containing TRY CATCH ROLLBACK pattern?

Personally, I find this more elegant than using table variables.

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Not sure if this would work (or I don't get this fully). I also want to log informational messages to the log table about every step (and the duration). Then I can also trace the last successful step in case of errors. Did you count that in? – Koen Jan 19 '12 at 15:07
  • @Koen: yes, you can use 2 variables that are set before each statement that are read in the CATCH block – gbn Jan 19 '12 at 15:14
  • Now I'd like to know why someone -1-ed this reply coz I just implemented a combo of both answers... – Koen Jan 20 '12 at 11:00