Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

to make procedure restartable

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
shyamaladevib
Asked:
shyamaladevib
  • 2
  • 2
1 Solution
 
mcv22Commented:
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
 
shyamaladevibAuthor Commented:
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
 
shyamaladevibAuthor Commented:
sorry forgot attaching it, please find attached
code-proc.doc
0
 
mcv22Commented:
Yeah, it looks good
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now