• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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