Update Table if Stored Proc Does Not Complete

  • 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?


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • <deleted misread question.>

  • 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?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    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