[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

to make procedure restartable

Posted on 2010-08-23
4
Medium Priority
?
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
mcv22 earned 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

656 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