Nested Transaction Handling Problem in SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Nested Transaction Handling Problem in SP

Author

Topic

khufiamalik
Posting Yak Master

120 Posts

Posted - 2008-07-29 : 07:30:32

Hello All,
I have 2 Sps (say SP1 and SP2) , SP2 is called from SP1(and ofcourse SP1 is main SP).
SP1 has it's own transaction and SP2 has its own transaction.

When I execute SP1, if no error occures then it works ok.
If any error exists the follwoing message is shown
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sample Code For Both SP is given below:
Create PROCEDURE SP1
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--Some Queries
exec SP2
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
END

Create PROCEDURE SP2
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--Some Queries
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
END

Thank you in advance.

Regard's
Yasir Mehmood

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-29 : 07:51:11

I guess you can use

if @@TRANCOUNT > 0 ROLLBACK

in SP1, rather than simply

ROLLBACK

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

cruxmagi
Starting Member

38 Posts

Posted - 2008-07-29 : 08:08:29

try with this

ALTER PROCEDURE SP1
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY

select * from Messages
exec SP2
COMMIT TRANSACTION
END TRY
BEGIN CATCH
if @@trancount>0
ROLLBACK transaction
END CATCH
END

khufiamalik
Posting Yak Master

120 Posts

Posted - 2008-07-30 : 01:02:25

quote:
Originally posted by cruxmagi

try with this

ALTER PROCEDURE SP1
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY

select * from Messages
exec SP2
COMMIT TRANSACTION
END TRY
BEGIN CATCH
if @@trancount>0
ROLLBACK transaction
END CATCH
END

Thanks , You solved my problem.

khufiamalik
Posting Yak Master

120 Posts

Posted - 2008-07-30 : 01:03:04

quote:
Originally posted by RyanRandall

I guess you can use
if @@TRANCOUNT > 0 ROLLBACK
in SP1, rather than simply
ROLLBACK

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Thanks , You solved my problem.

khufiamalik
Posting Yak Master

120 Posts

Posted - 2008-07-30 : 03:13:54

Now My Transaction is working fine, but when ever a error is raised, I get the given below error with my own erorr.

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.

Can You help me in this regard.

Thanx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources