Re: how to roll back

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Andras Jakus (andras.jakus_at_vodafone.com)
Date: 03/29/04 

Date: Sun, 28 Mar 2004 20:26:06 -0800

HI Greg,

the "set @err = @err + @@error" command set the @@error to 0, this script never make rollback.
Use the "if @err != 0" command!

JBandi
     
     ----- Greg Linwood wrote: -----
     
     Hi pk.
     
     You can only use rollback within a transaction block. If you commit a
     transaction, there's no way to roll it back later.
     
     A quick demo of how to use rollback in tsql is:
     
     declare @err int
     declare @err = 0
     
     set xact_abort off -- set off or on for auto rollback on any error
     set transaction isolation level read committed -- sets isolation (locking)
     level
     begin transaction
     
      update table1 set column1 = 'a' where columnpk = 123
      set @err = @err + @@error
     
      insert into table2 (column2, column3) values ('1', 123)
      set @err = @err + @@error
     
     if @@error != 0
      rollback
     else
      commit
     
     HTH
     
     Regards,
     Greg Linwood
     SQL Server MVP
     
     "pk" <pk@> wrote in message news:epHxmqTFEHA.3424@tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
>> Use Query Analyzer, BEGIN TRAN ...... COMMIT TRAN, then ROLLBACK TRAN to
> undo the changes. And only applicable for UPDATE & DELETE queries within
     the
> transaction.
>> just want to understand more how to use rollback and how it works.
>> tks
> pk
>>

Relevant Pages

  • Re: Classic Nest SP with Transaction Question
    ... enclosing transaction or should begin and commit/rollback its own ... > I echo Ron's points and also have been using an approach very similar to> Tom's method of using a SAVE PT instead of starting a new TRANSACTION if the> ChildSP is called from the ParentSP. ... > The ParentSp now has to ROLLBACK everything up to that point. ... As Ron> mentioned, the ParentSP may have called many child SPs, and performed many> updates up to this point, and all of these need to be rolled back. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... PMFJI, but if your child proc is using an explicit tran, then it can be coded as follows: create proc dbo.ChildProc as set nocount on declare @trancount int set @trancount = @@TRANCOUNT if @trancount> 0 begin tran ChildProcTran else save tran ChildProcTran /* ... Do some stuff */ if @@ERROR> 0 begin raiserror rollback ChildProcTran return end ... commit tran go ... I believe I'm having the same issue as Chad with nested stored procedures inside a transaction. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... This will take care of what is done in the child, but what I want to do is ... rollback the entire outer transaction - the one initiated in the outer SP. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... Tom's method of using a SAVE PT instead of starting a new TRANSACTION if the ... ChildSP is called from the ParentSP. ... The ParentSp now has to ROLLBACK everything up to that point. ... it out erasing all record of the error in the Child. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... Tom's method of using a SAVE PT instead of starting a new TRANSACTION if the ... ChildSP is called from the ParentSP. ... The ParentSp now has to ROLLBACK everything up to that point. ... it out erasing all record of the error in the Child. ...
    (microsoft.public.sqlserver.programming)