how to roll back

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
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
>
|||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 ?
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> pk
|||Yes - my bad there! Thanks for picking it up (:
Regards,
Greg Linwood
SQL Server MVP
"Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
news:C4555093-2AD6-44DE-A43E-CCF5792D49E3@.microsoft.com...
> 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...
TRAN to
within
> the
|||In addition to Greg's response and to directly answer your question,
rollbacks affect not only updates and deletes, but inserts also.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"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
>
|||Hi all,
I placed the SET XACT_ABORT ON statement introduced by Greg at the beginning of a sproc, but appearently it did not skip the t-sql errors happended within the sproc and rollback. I'm hoping to find a method to skip all error messages and just rollback.
Is there a way to do that?
Thanks,
-Lawrence
"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...
> the
>
>