Rollback gives error

Question

Rollback gives error

srinikrish

SSC-Addicted

Points: 457
More actions
January 29, 2014 at 12:28 am

#282890

When I have a rollback transaction in the stored procedure and the parent script also has a begin tran then it gives the following error. How can the error be stopped.
create proc abc
as
begin
create table #tmp
(seqint )
begin tran
insert into #tmp
select 1 union
select 2
rollback tran
end
------------------------------------------------------------------------
begin tran
exec abc
if @@trancount > 0
rollback tran
When abc is executed with begin tran, folloiwng message appears
Server: Msg 266, Level 16, State 2, Procedure abc, Line 19
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What I would suggest - don't nest transactions and do all your transaction handling at one level

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

srinikrish SSC-Addicted Points: 457 More actions · Answer 2

I have a situation where this happens as the store procedure is called from multiple places and a particular situation demands multiple transaction opening. The problem happens only when the store procedure fails and rollback happens.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You can still not nest transactions, it just requires a little more work around whether there's an open transaction, whether to start another transaction, when to rollback, etc.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Nadrek SSC-Insane Points: 20039 More actions · Answer 4

GilaMonster (1/29/2014)
What I would suggest - don't nest transactions and do all your transaction handling at one level

+1 - there are no nested transactions, despite what it looks like. ROLLBACK always rolls back all transactions, per Paul Randall (http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/[/url]).