Re: how to roll back

From: Greg Linwood (g_linwoodQhotmail.com)
Date: 03/29/04 

Date: Mon, 29 Mar 2004 13:32:17 +1000

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
    ... 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: sql question
    ... > begin tran ... > together to enable the rollback if the data being changed it not ... command to SQL Server and then commit or rollback as needed. ... In the first batch, you issue a BEGIN ...
    (microsoft.public.sqlserver.programming)
  • 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: DBD::Sybase and auto commit
    ... >> rollbacks into the stored proc. ... >> EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. ...
    (perl.dbi.users)
  • Re: SQL SERVER Rollback Problems
    ... It will not be fully committed until you issue a COMMIT TRAN. ... If your sp had an error or was manually stopped the transaction is still ... issue the ROLLBACK ALL operations are rolled back up until that first BEGIN ... > execution. ...
    (microsoft.public.sqlserver.programming)