Next transaction/rollback 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 2000 Forums

SQL Server Development (2000)

Next transaction/rollback problem in SP

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-09 : 09:04:59

Prabhu writes "The SP WORKS FINE BUT IT DISPLAYS THIS ERROR

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

CREATE PROCEDURE [dbo].[_ObsoleteFreeze_Execute]
@RetVal INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
--SET XACT_ABORT ON
DECLARE @ErrMsg NVARCHAR(4000)
BEGIN TRY
BEGIN TRAN
EXEC _Obsolete_GetCode 'F', @RetVal
UPDATE ItemArchivalStatus SET New = 'N' WHERE Status = 'F' AND New = 'Y'
COMMIT TRAN
SET @RetVal = 1 -- Update is successful, return Positive value
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT @RetVal = -ERROR_NUMBER() -- Insert/Update failed, return ErrorId as negative number
END CATCH
END
RETURN @RetVal

and the _Obsolete_GetCode stored procedure will look like this

CREATE PROCEDURE [dbo].[_Obsolete_GetCode ]
@RetVal INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @ErrMsg NVARCHAR(4000)
BEGIN TRY
BEGIN TRAN
EXEC _ObsoleteChunks_AddUpd 'F', @RetVal
COMMIT TRAN
SET @RetVal = 1 -- Update is successful, return Positive value
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT @RetVal = -ERROR_NUMBER() -- Insert/Update failed, return ErrorId as negative number
END CATCH
END
RETURN @RetVal

The structure of _ObsoleteChunks_AddUpd will look like same as the _Obsolete_GetCode stored procedure [i.e will have Begin Tran, Commit Tran & Rollback Tran]
When i some error/exception in the _ObsoleteChunks_AddUpd stored procedure, all the transaction are rolling back properly but i'm getting a message "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION". How to solve this problem?"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-09 : 11:22:05

Before writing new BEGIN TRANS, check the value of @@TRANCOUNT. If it is 0, then only begin new transaction, to avoid such kind of problems.

Like this:

If @@TRANCOUNT = 0 then
BEGIN TRANSACTION

In your case I think the problem is caused because in the _Obsolete_GetCode when ROLLBACK TRAN is called it cancels all active transaction even the calling SP's transaction. So the ROLLBACK TRAN
in the _ObsoleteFreeze_Execute is throwing the error.

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources