Solved

to make procedure restartable

Posted on 2010-08-23
4
298 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Better way to make a query with date filter. 5 43
Amazon RDS migrate to SQL Server 3 34
Using this function 4 42
SQL Select Query help 1 38
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 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