Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error Rolling back inner transaction Expand / Collapse
Author
Message
Posted Monday, May 12, 2008 9:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 17, 2013 6:28 AM
Points: 54, Visits: 37
Hi,

When i execute the following Script.
I get error 'Cannot roll back InnerTran. No transaction or savepoint of that name was found.'
i.e I couldn't able to rollback inner transaction.
Can any one know the reason for this?

Create Table BPTest(id int)
begin try
begin tran OuterTran
Insert into BPTest values (1)
begin try
begin tran InnerTran
Insert into BPTest values (2)
Declare @Error varchar(500)
set @Error='test'
RAISERROR(@Error, 16, -1)
commit tran InnerTran
end try
begin catch
rollback tran InnerTran
end catch
Insert into BPTest values (3)
rollback tran OuterTran
end try
begin catch
print Error_Message()
end catch
Select * from BPTest
drop table BPTest

Thanks in advance,
Regards,
B. Prakash


To Get Succeeded in Life don't get Changed according to the environment,

Be Honest, Smart & Bold enough to create your own fine environment.

Regards,
B.Prakash.
Post #498931
Posted Monday, May 12, 2008 1:08 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
I think you want to use a transaction save point:

Create Table #BPTest(id int)
begin try
begin tran OuterTran
Insert into #BPTest values (1)
begin try
save tran InnerTran
Insert into #BPTest values (2)
Declare @Error varchar(500)
set @Error='test'
RAISERROR(@Error, 16, -1)
end try
begin catch
rollback tran InnerTran
end catch
Insert into #BPTest values (3)
end try
begin catch
print Error_Message()
rollback tran OuterTran
end catch
Select * from #BPTest
drop table #BPTest
Post #499056
Posted Monday, January 31, 2011 4:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 25, 2013 12:17 AM
Points: 19, Visits: 38
So we cannot have nested Transactions (it does not make any sense to have nested transaction when we are not allowed to rollback the inner transaction) in SQL Server? And if we want nested transaction we should use Save Point instead?

In other words in SQL Server nested transactions can be implemented by using Save Points? Kindly explain this.
Post #1056074
Posted Monday, January 31, 2011 4:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 40,430, Visits: 36,881
Nested transactions are a lie.
http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%282630%29-nested-transactions-are-real.aspx



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1056079
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse