locked
Err msg 266 - mismatching number of Begin and Commit statements RRS feed

  • Question

  • User2103134756 posted

    I'm using a custom role provider and I want to update the usersInRoles table when I add, update, or delete a user. I have the same pattern of logic on the Add and Update except I check the @@Error before using return. I didn't need to save transaction because the rollback seemed to have worked. On the Delete, that's not the case so, I'm using the save transaction to see if it rolls back. What I'm testing for is if there is a problem with deleting the user in the userInRoles table, the delete transaction will be restored so there's orphan record. So, I'm forcing a null on the DeleteUserInRole proc. I have not tested it from C# yet. I've played around with moving the Begin and Save transactions but I still get an error.

    USE [MYDB]
    GO
    /****** Object:  StoredProcedure [dbo].[DeleteUser]    Script Date: 06/17/2019 18:29:45 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		
    -- Create date: <Create Date,,>
    -- Description:	Deletes Admin from Admins table
    -- =============================================
    ALTER PROCEDURE [dbo].[DeleteUser]
    	-- Add the parameters for the stored procedure here
    	--(@UserName VarChar(256),
    	 --@RoleName VarChar(255))
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT OFF;
    	
    	Declare @T INT;
    	--Begin Transactionn
    	BEGIN TRANSACTION T
    	
    	SAVE TRANSACTION ROLLBACK_SAVE
    
    	--Delete the user
    	DELETE FROM Users WHERE UserName = 'MYUSERNAME'
    	--DELETE FROM Users WHERE UserName = @UserName
        
    	--If error return
    	IF @@ERROR <> 0
    		BEGIN
    			RETURN -1
    		END
    		
    	--Delete Role from UsersInRoles table
    	EXEC DeleteUserInRoll 'MYUSERNAME', Null
    	--EXEC DeleteUserInRoll @UserName, @RoleName
    	
    	--If error rollback delete transaction
    	IF @@TRANCOUNT > 0
    		BEGIN
    			ROLLBACK TRANSACTION ROLLBACK_SAVE
    			RETURN -1
    		END
    	
    	
    	--Commit transaction
    	COMMIT TRANSACTION T
    	RETURN 0
    
    END
    

    Tuesday, June 18, 2019 1:09 AM

All replies

  • User-2082239438 posted

    I am not able to understand your scenario, but implementing the try catch block will work for you. You will need to use the below scenario.

    ALTER PROCEDURE [dbo].[DeleteUser]
    	-- Add the parameters for the stored procedure here
    	--(@UserName VarChar(256),
    	 --@RoleName VarChar(255))
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT OFF;
    	
    	Declare @T INT;
    
    	BEGIN TRY
    		BEGIN TRANSACTION T 
    	
    			------------DELETE THE USER------------
    			DELETE FROM Users WHERE UserName = 'MYUSERNAME'
        
    	
    			EXEC DeleteUserInRoll 'MYUSERNAME', Null
    			--EXEC DeleteUserInRoll @UserName, @RoleName
    
    			IF @@TRANCOUNT>0
    			BEGIN
    				COMMIT TRANSACTION T;
    			END
    		RETURN 0;
    
    	END TRY
    	BEGIN CATCH
    			ROLLBACK TRANSACTION T
    			RETURN -1
    	END CATCH
    END

    Tuesday, June 18, 2019 9:37 AM
  • User2103134756 posted

    Thanks for the input. That worked for the error message that I was getting but I still have the same problem as before. I'm forcing a null on the second call to delete the role record to force an error. The user record gets deleted but the role record does not because of the error. What I need is the user record to be rolled back if there is an error on the role record call. I'm thinking I would need to Save the transaction point.

    Tuesday, June 18, 2019 4:08 PM
  • User-2082239438 posted

    As shared, you will rollback the entire process if you will get error in the Call of the statement 

    EXEC DeleteUserInRoll 'MYUSERNAME', NULL

    Above shared query will work, if you will get error in any of the statement, entire Transaction will get rollback.

    Wednesday, June 19, 2019 5:21 AM
  • User753101303 posted

    Hi,

    And what are you doing in DeleteUserInRoll ? I believe you could have this wrong behavior if using transaction points as well. For now it seems to me you just want a single transaction so I'm not sure why yoou thought you need transaction points (to me it's only useful if you really want to commit just a part of a transaction).

    Wednesday, June 19, 2019 5:19 PM