Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in your
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
Showing posts with label roll. Show all posts
Showing posts with label roll. Show all posts
Monday, March 19, 2012
How to rollback properly
Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in you
r
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as thin
gs
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as thin
gs
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in you
r
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as thin
gs
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as thin
gs
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
How to rollback properly
Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?
Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in your
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?
Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in your
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
How to roll back the replication?
Hi, all.
I make a db replacated as distributor.
I decided later removing replication.
But, I don't know how to remove rowguid column from all tables.
How can I set back to the initial state of DB before replication?
thank you..You can't that I know of. You will need to alter the tables and drop the columns not needed.
I make a db replacated as distributor.
I decided later removing replication.
But, I don't know how to remove rowguid column from all tables.
How can I set back to the initial state of DB before replication?
thank you..You can't that I know of. You will need to alter the tables and drop the columns not needed.
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
pkHi 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 ne
ver 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 with
in the sproc and rollback. I'm hoping to find a method to skip all error me
ssages 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
>
>
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
pkHi 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 ne
ver 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 with
in the sproc and rollback. I'm hoping to find a method to skip all error me
ssages 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
>
>
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
>
>
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
>
>
Subscribe to:
Comments (Atom)