Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1

Question

This Sql Server Stored Procedure gives me the

Error :

(Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.):

SQL:

DECLARE @MessageType sysname
DECLARE @ConversationHandle uniqueidentifier
DECLARE @RawRemittanceAdviceId int
DECLARE @ClrReturnCode int
DECLARE @WebServiceUrl nvarchar(512)
DECLARE @ErrorMessage nvarchar(MAX)

BEGIN 
BEGIN TRANSACTION

WAITFOR (
            RECEIVE TOP (1)
                  @MessageType = message_type_name
                , @RawRemittanceAdviceId = message_body
                , @ConversationHandle = conversation_handle
            FROM [RawRATranslationChannel]
        ), TIMEOUT 5000 -- 5 seconds

IF (@@ROWCOUNT = 0)
    BEGIN
        ROLLBACK TRANSACTION
    END
ELSE
    BEGIN
    -- Check for end of dialog messages
    IF (@MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
        BEGIN
            END CONVERSATION @ConversationHandle
        END
    ELSE IF (@MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
        BEGIN
            -- Handle the error
            BEGIN
                -- Add Error Message
                UPDATE [dbo].[RawRemittanceAdvice]
                SET DiagnosticMessage = N'Service Broker Error', ProcessingStatus = 2, LastUpdated = GETUTCDATE()
                WHERE RawRemittanceAdviceId = @RawRemittanceAdviceId
            END
            END CONVERSATION @ConversationHandle
        END
    ELSE IF (@MessageType = 'TranslateRawRA')
        -- Perform Translation of the Raw Remittance Advice
        BEGIN
            BEGIN TRY

                SELECT @WebServiceUrl = SettingValue
                FROM dbo.GlobalSettings
                WHERE SettingName = N'TranslationWebServiceUrl'

                -- Call CLR Sproc to perform Translation
                EXEC @ClrReturnCode = [dbo].[TranslateRawRemittanceAdvice] @RawRemittanceAdviceId, @WebServiceUrl, @ErrorMessage OUTPUT

                IF @ClrReturnCode = 0
                    BEGIN
                    -- Send Response
                    SEND ON CONVERSATION @ConversationHandle
                    MESSAGE TYPE [TranslateRawRAResponse]
                    (CAST(@RawRemittanceAdviceId AS NVARCHAR))
                    END
                ELSE
                    BEGIN
                        -- Add Error Message
                        UPDATE [dbo].[RawRemittanceAdvice]
                        SET DiagnosticMessage = @ErrorMessage, ProcessingStatus = 2, LastUpdated = GETUTCDATE()
                        WHERE RawRemittanceAdviceId = @RawRemittanceAdviceId
                    END

            END TRY

            BEGIN CATCH
                BEGIN
                    -- Add Error Message
                    UPDATE [dbo].[RawRemittanceAdvice]
                    SET DiagnosticMessage = ERROR_MESSAGE(), ProcessingStatus = 2, LastUpdated = GETUTCDATE()
                    WHERE RawRemittanceAdviceId = @RawRemittanceAdviceId
                END
            END CATCH

            END CONVERSATION @ConversationHandle

        END
    COMMIT TRANSACTION
END
END

[http://stackoverflow.com/questions/21930156/transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-comm](http://stackoverflow.com/questions/21930156/transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-comm) has a good answer that may help — ikariw, Jan 20 '15 at 10:30

score 0 · Answer 1 · answered Sep 01 '14 at 13:36

0

See below workarounds for your reference:

Try PRINT @@TranCount on suspecting areas of your sql code, to get the count of transactions.
Try to execute the same by DEBUG enabled.

answered Sep 01 '14 at 13:36

Karthik Kola

93
5

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1

1 Answers1