Solved

to make procedure restartable

Posted on 2010-08-23
4
285 Views
Last Modified: 2012-06-21
Hi Experts,
the The problem is that the procedure is not restartable, so when it failed the first time, it left data in Accounts which was causing PK violations on the subsequent run. please suggest necessary changes to be made:

code-proc.doc
0
Comment
Question by:shyamaladevib
  • 2
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
mcv22 earned 500 total points
ID: 33503033
Wrap the insert and update to dbo.AFR_Accounts in a transaction. This way, it will rollback the changes made if it doesn't make it all the way to the end

BEGIN TRY
  BEGIN TRANSACTION
     Insert dbo.AFR_Accounts ....

     Update dbo.AFR_Accounts ...
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
END CATCH
0
 

Author Comment

by:shyamaladevib
ID: 33503816
Hi MCV, thanks a lot, please find attached code and please review it and let me know if my placement for the roll back transaction is correct.
Thanks
0
 

Author Comment

by:shyamaladevib
ID: 33503827
sorry forgot attaching it, please find attached
code-proc.doc
0
 
LVL 12

Expert Comment

by:mcv22
ID: 33503834
Yeah, it looks good
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question