Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Cape Town

    Unanswered: How to manage stored procedure transaction involving update in several tables

    I am running a vba procedure ( adp file ) that executes successively 5 stored procedures . however it happens that the execution breaks at the middle of the code thus giving a situation where only 2 tables among 5 are updated.

    Is it any solution to rollback transactions update already done before
    the code breaks due to error ?

    I was thinking about combining all stored proc on a big one and use
    Begin transaction - commit transaction and rollback transaction ... however i am not sure wheter updates involving several tables can be handled on one transaction.

    Any advise highly appreciated !

  2. #2
    Join Date
    Jul 2004
    you can roll back the database by restoring the last transaction log made, but that is a double edged sword because it depends on how often you set sql server to make transaction logs, so you'll either lose the data that you want removed, or you coud lose a day (for example).

    But if you restore the last made transaction log then you'll be able to go back to this vba problem of yours.

    one thing im wondering is couldnt you just re-run the remaining 3 procedures to update the rest of the tables, create a backup and go from there?

  3. #3
    Join Date
    Jul 2003
    San Antonio, TX
    ...Hmmmmm...I think the poster is talking about the functionality of his/her application, not how to rollback transactions...

    One way (don't recommend!!!) is to use a connection object before invoking the first procedure with ".BeginTransaction" method. This way you can check for errors by querying the connection object and decide whether to continue or rollback after each procedure you call.

    Another way is to create a procedure that would be a wrapper for calling all other procedures, where you can check for errors after each call.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts