June 13, 2007 at 11:52 pm
Hi,
I was wondering if I could get some advice on how best to do this.
The current situation is, I need to undue a deposit charge that was made to about 50 accounts, and after crediting or debiting the payment allocation table (depending what the balance is), I need to complete all new transactions of the credit for each account in the tranactions table. (Assume the payment is the same for every account $30.00) How can I create transactions all at once through a query? Here are the rules---
Credit all payment allocations that are allocated to Deposits Received and Returned using today’s timestamp.
(PaymentAllocation Table)
Credit all non-deleted Deposits Received and Returned transactions using today’s timestamp.
(Transactions Table)
Mark all non-deleted Deposit Received and Returned transactions as Deleted and not Visible.
(Transactions Table)
Run an automated payment allocation script to automatically allocate the credited payment allocations.
I am sorry if this seems complex, but I have been struggling with this for several days. Thank you.
June 14, 2007 at 12:06 am
You can bring all under the same transaction. So that i you get error at then end the whole transaction is rolled back.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 14, 2007 at 7:43 am
Do you plan to do this in Query Analyzer/SQL Stuido, a command line script, or an application? I would think that Query Analyzer would be the best bet but it can be done through any of them. Here is how I would normally tackle something this complex, especially if its a critical customer affecting production system:
1. Backup production system and restore to development system (always good to practice before commiting yourself).
2. Hopefully your final "automated payment allocation script" is an SP, but a "real" script I would make a copy of it and then adjust it wrap a transaction at the start, add the three beginning steps you outlined above then let the rest of script take over.
3. At the very end either commit or rollback your transaction. Most errors encountered along the way "should" rollback everything, however depending on the type of error it is possible that only a local statement rolls back. See http://www.sommarskog.se/error-handling-I.html#whenwhichaction for a good reference on how/what errors roll everything back.
4. Test the rollback and make sure everthing looks right.
5. Repeat and test the commit and make sure everything looks right.
6. Rebackup the productions system and "go for it".
NOTE: Be sure an keep everything that you do within a single connection to the database (i.e. a single Query Analyzer session) otherwise parts of your changes will be outside the scope of your transaction.
HTH,
James.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply