HELP - The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Question

HELP - The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Fabio Reis Martins

Mr or Mrs. 500

Points: 508
More actions
August 13, 2008 at 9:12 am

#196968

I have a StoredProcedure above, but it's not run.. I receive an SQL error "Msg 3903, Level 16, State 1, Procedure usp_AlteraPerfil_gc, Line 44 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." Pelase what is wrong?
CREATE PROCEDURE usp_AlteraPerfil_gc
@PerfilID int,
@PerfilGrupo nvarchar(250),
@idiomaID int = 1,
@menuVersao int = 0
AS
BEGIN
DECLARE @Sql as nVarChar(1200)
DECLARE @PerfilGrupoFinal as nVarchar(1000)
DECLARE @err1 int
SET NOCOUNT ON;
BEGIN
IF EXISTS (SELECT id_perfil FROM tbl_perfil WHERE id_perfil = @PerfilID)
BEGIN
--SET XACT_ABORT ON
BEGIN TRAN
BEGIN
SET NOCOUNT ON DELETE FROM tbl_perfil_acesso WHERE id_perfil = @PerfilID;
IF @@ERROR <> 0 GOTO ErrBlock
Set @PerfilGrupo = left(@PerfilGrupo,len(@PerfilGrupo)-1)
Set @PerfilGrupoFinal = right(@PerfilGrupo,len(@PerfilGrupo)-1)
Set @Sql ='SET NOCOUNT ON INSERT INTO tbl_perfil_acesso
SELECT id_Menu, id_idioma, MenuVersao, '+CAST(@PerfilID AS varChar(12))+' as id_perfil FROM tbl_menu
WHERE id_Menu in ('+@PerfilGrupoFinal+') AND id_idioma ='+CAST(@idiomaID AS varChar(12))+' AND menuVersao='+CAST(@menuVersao AS varChar(12))+';'
Exec(@Sql)
IF @@ERROR <> 0 GOTO ErrBlock
COMMIT
Select 'ok' as result
ErrBlock:
ROLLBACK
Select 'nok' as result
END
END
ELSE
BEGIN
Select 'nok' as result
END
END
END
EXEC usp_AlteraPerfil_gc 1,'#1,2,3,4,5,6,7,8,9,10,11,12,13,14#',9
TKS

Viewing 3 posts - 1 through 2 (of 2 total)

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

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

The code flow is such that after the commit is reached, execution goes past the Errblock label and executes rollback. Of course, seeing as the transaction has just committed, there's no open transaction to rollback.

Perhaps something like this

BEGIN

DECLARE @Sql as nVarChar(1200)

DECLARE @PerfilGrupoFinal as nVarchar(1000)

DECLARE @err1 int

SET NOCOUNT ON;

BEGIN

IF EXISTS (SELECT id_perfil FROM tbl_perfil WHERE id_perfil = @PerfilID)

BEGIN

--SET XACT_ABORT ON

BEGIN TRAN

SET NOCOUNT ON DELETE FROM tbl_perfil_acesso WHERE id_perfil = @PerfilID;

IF @@ERROR <> 0 GOTO ErrBlock

Set @PerfilGrupo = left(@PerfilGrupo,len(@PerfilGrupo)-1)

Set @PerfilGrupoFinal = right(@PerfilGrupo,len(@PerfilGrupo)-1)

Set @Sql = 'SET NOCOUNT ON INSERT INTO tbl_perfil_acesso

SELECT id_Menu, id_idioma, MenuVersao, '+CAST(@PerfilID AS varChar(12))+' as id_perfil FROM tbl_menu

WHERE id_Menu in ('+@PerfilGrupoFinal+') AND id_idioma ='+CAST(@idiomaID AS varChar(12))+' AND menuVersao='+CAST(@menuVersao AS varChar(12))+';'

Exec(@Sql)

IF @@ERROR <> 0 GOTO ErrBlock

COMMIT

Select 'ok' as result

Goto AfterErrBlock

ErrBlock:

ROLLBACK

Select 'nok' as result

AfterErrBlock:

END

ELSE

BEGIN

Select 'nok' as result

END

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

Fabio Reis Martins Mr or Mrs. 500 Points: 508 More actions · Answer 2

Tanks, now it's working fine.

God Bless You

Fábio Reis Martins

Brasil