May 3, 2016 at 4:18 am
Is it possible to update a logging table if the proc that writes to it fails?
I have a stored proc that is reloading a large-ish fact table and as part of this load it updates a log table with a row for each date that is loaded. I've already got code in place to write to the log table if an individual day fails but I'm wondering if there's some way of updating this table if the proc is cancelled or fails.
Currently the table has columns for the start and end of an execution cycle, which is a block of dates, columns for the start and end of each date load, columns for the number of rows loaded to the staging and fact tables and a column indicating success or failure of each date load. When I start the proc, the execution start column is populated but the execution end column is left as NULL. At the start of each date run, the start column is updated but the end column is only updated when the date has been loaded or that date load fails. The execution end column is only updated when the execution cycle completes.
I've got error handling in place to update the log table if anything goes wrong but I'd like to find some way of updating the execution end if the proc is deliberately stopped. I suspect I won't be able to put something on my load proc. If the proc is stopped it can't do anything. Is there a way to do this?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
May 3, 2016 at 6:35 am
<deleted misread question.>
May 3, 2016 at 6:59 am
If the procedure is stopped and it's properly written, it might have to rollback everything that has been done. Because you're using a single transaction, right?
May 3, 2016 at 7:54 am
Luis Cazares (5/3/2016)
If the procedure is stopped and it's properly written, it might have to rollback everything that has been done. Because you're using a single transaction, right?
AARGH! Session timeout!! I'll try and explain again.
I'm not hugely familiar with transactions and rollbacks to be honest. I'm the reporting guy so I when I write a stored proc it's usually a SELECT statement to populate a report. I've recently inherited the data warehouse so I'm looking more into UPDATE and INSERTS etc.
One of the tasks I've got is to rebuild one of the larger fact tables because we've found errors in it. Most of the fact tables are truncated and reloaded from scratch each night but this one is loaded with the previous day's data only. In order to fix the errors the plan is to build another version in parallel with the correct logic then switch over when the rebuild is complete.
I've written a proc that first truncates and loads the new staging table for a given date then calls the procedure to load the fact table before moving on to the next date. I only use BEGIN TRAN when I'm calling the fact table load. My thinking was to compare the row count of the staging table and the fact table then commit the transaction only if the fact count was equal to or lower than the staging count. If it was greater than there might be duplicates so I would roll back the fact load and move on to the next date. If the transaction was rolled back, the proc updates the log table and sends an email.
The update load will only be run during working hours so if there are any problems we'll be able to investigate immediately.
I'm prepared to be told this is a horribly bad approach though.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply