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:33

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

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-29 : 07:49:16

Create PROCEDURE SP1
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
--Some Queries
COMMIT TRANSACTION
END TRY

BEGIN CATCH
ROLLBACK
END CATCH
END

Chk this..,

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-29 : 08:06:59

duplicate:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107597

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources