Nested Transactions

Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer transaction's statements executing?Use SAVE TRAN in you "inner transaction" instead of BEGIN TRAN. That gives y
ou a savepoint to roll
back to.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:uIP6f6FSGHA.4976@.TK2MSFTNGP11.phx.gbl...
Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer
transaction's statements executing?|||You can't. From SQL BOL:
"Naming multiple transactions in a series of nested transactions with a
transaction name has little effect on the transaction. Only the first
(outermost) transaction name is registered with the system. A rollback
to any other name (other than a valid savepoint name) generates an
error. None of the statements executed before the rollback are in fact
rolled back at the time this error occurs. The statements are rolled
back only when the outer transaction is rolled back."|||Hi rmg66
Rollback can only be used to roll back to the outermost begin tran, or to a
savepoint. Note that there really is not concept of nesting transactions in
SQL Server. Please read about transaction control in the Books Online.
begin tran One -- the label here is useless
insert into XX (data) values ('Tran1')
SAVE tran Two -- this does not start a new transaction, it only marks a spot
in the log that we can roll back to
insert into XX (data) values ('Tran2')
rollback tran Two -- roll back to named savepoint
insert into XX (data) values ('Tran1')
Commit tran One -- the label here is useless
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:uIP6f6FSGHA.497
6@.TK2MSFTNGP11.phx.gbl...
Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer transaction's statements executing?