Error/Exception handling in sql server 2000

• matthew.peters

  Ten Centuries

  Points: 1138

  More actions

  August 1, 2012 at 6:18 pm

  #265066

  Hello

  I'm trying to create a template to use for data fixes. The problem I'm having is running a bunch of statements in one transaction, If one of the statement's produces an error I want the whole transaction along with all the statements to be rolled back. But I can seem to make this happen. With the template below the first statement succeeds but the second doesn't, I want both to be rolled back when the second statement hits an error

  use <--database Name-->

  begin transaction

  <--Valid statement-->

  <--Invalid statement that cause error-->

  IF @@Error <> 0

  ROLLBACK TRAN

  else

  commit transaction

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  August 1, 2012 at 6:57 pm

  #1520608

  Try reading this to see if it helps you, if NOT come on back and someone can give you further assistance

  http://msdn.microsoft.com/en-us/library/ms188790.aspx

  Although the link is to a reference to sQL 2005, the technique is adaptable to use in SQL 2000

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]