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

Transact-SQL (2000)

Next transaction/rollback problem in SP

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-08 : 07:42:58

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?"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-08-08 : 14:20:33

One thought:
If you objective is to commit or rollback all transactions (all or nothing) then I would suggest controlling a single transaction from the outer most SP. Remove transactions from called SPs. Use ReturnCodes to communicate success/failure to your calling SP and commit or rollback accordingly (all or nothing at the highest level).

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources