SQL Server transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

0
votes

In the below stored procedure, I'm getting the error mentioned in some cases, what am I doing wrong here?

Error:

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

Stored procedure:

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[prcInsDataFeesForVersion]   
    (@ContractId  INT,
     @UserId      INT,
     @VersionNo   INT,
     @SelectAll   BIT,
     @SubProductList NVARCHAR(MAX),
     @ConvRateFlag BIT)    
AS  
BEGIN  
    SET NOCOUNT ON  

    DECLARE @RetVal INT,  
            @ErrMsg VARCHAR(255),
            @FunctionId INT = 1234,    
            @AppId VARCHAR(20) = 'ID1021',
            @FeesOverrideFlg BIT = 1,
            @ContractProdFeeVerSummId INT

    CREATE TABLE #tblMasterData
    (
        ProdCd INT,
        ProdName NVARCHAR(255),
        SubProdCd INT,
        SubProdName NVARCHAR(255),
        ProdTypeCd INT,
        ProdDeliveryTypCd INT, 
        ProdStatusCd INT,
        ProdStatusDt DATETIME,
        CreatedDate DATETIME
    )

    CREATE TABLE #tblContractInterCulturalDataFiltered
    (
                        ContractID              INT
                      , ContractProdID          INT
                      , ProdCd                  INT
                      , ProdName                NVARCHAR(255)
                      , ContractSubProdID       INT
                      , SubProdCd               INT
                      , SubProdName             NVARCHAR(255)
                      , ContractProdStatusCd    INT
                      , ContractProdStatusDt    DATETIME
                      , ContractSubProdStatusCd INT
                      , ContractSubProdStatusDt DATETIME
                      , ContractProdFeeId       INT
                      , FeeTypCd                INT
                      , RowMatrixParameterCd    INT
                      , CreatedDate             DATETIME
                      )

              CREATE TABLE #tblContractInterCulturalDataFilteredInsert
                      (
                        ContractID               INT
                      , ContractProdID           INT
                      , ProdCd                   INT
                      , ProdName                 NVARCHAR(255)
                      , ContractSubProdID        INT
                      , SubProdCd                INT
                      , SubProdName              NVARCHAR(255)
                      , ContractProdStatusCd     INT
                      , ContractProdStatusDt     DATETIME
                      , ContractSubProdStatusCd  INT
                      , ContractSubProdStatusDt  DATETIME
                      , FeeTypCd                 INT
                      , FeeDeterminantCd         INT
                      , RowMatrixParameterCd     INT
                      , ProdFeeCurrCd            INT
                      , RowParameterValueCd      INT
                      , ColumnParameterValueCd   INT
                      , ProdFeePct               REAL
                      , RangeFromDayCnt          INT
                      , RangeToDayCnt            INT
                      , ProdFeeLevelMinAmt       DECIMAL
                      , ProdFeeLevelMaxAmt       DECIMAL
                      , ProdFeeAmt               DECIMAL
                      , ContractProdFeeId        INT
                      , CreatedDate              DATETIME
                      )

              CREATE TABLE #tblContractInterCulturalDataFilteredUpdate
                      (
                        ContractID               INT
                      , ContractProdID           INT
                      , ProdCd                   INT
                      , ProdName                 NVARCHAR(255)
                      , ContractSubProdID        INT
                      , SubProdCd                INT
                      , SubProdName              NVARCHAR(255)
                      , ContractProdStatusCd     INT
                      , ContractProdStatusDt     DATETIME
                      , ContractSubProdStatusCd  INT
                      , ContractSubProdStatusDt  DATETIME
                      , FeeTypCd                 INT
                      , FeeDeterminantCd         INT
                      , RowMatrixParameterCd     INT
                      , ProdFeeCurrCd            INT
                      , RowParameterValueCd      INT
                      , ColumnParameterValueCd   INT
                      , ProdFeePct               REAL
                      , RangeFromDayCnt          INT
                      , RangeToDayCnt            INT
                      , ProdFeeLevelMinAmt       DECIMAL
                      , ProdFeeLevelMaxAmt       DECIMAL
                      , ProdFeeAmt               DECIMAL
                      , ContractProdFeeId        INT
                      , CreatedDate              DATETIME
                      )             

              CREATE TABLE #tblSelContractSubProds
              (             
                      ContractSubProdId            INT
              )

              BEGIN TRY  

              BEGIN TRANSACTION

              IF(@SelectAll=0 AND @SubProductList IS NOT NULL)
              BEGIN
                      INSERT INTO #tblSelContractSubProds 
                      SELECT intValue FROM dbo.udfSplit(@SubProductList,',',1)
              END

              INSERT INTO #tblMasterData(
                        ProdCd
                      , ProdName
                      , SubProdCd
                      , SubProdName
                      , ProdTypeCd
                      , ProdDeliveryTypCd  
                      , ProdStatusCd
                      , ProdStatusDt
                      , CreatedDate
                      )
                      SELECT P.ProdCd
                      , PC.Descr
                      , S.SubProdCd
                      , SC.Descr
                      , P.ProdTypeCd
                      , P.ProdDeliveryTypCd
                      , P.ProdStatusCd
                      , P.ProdStatusDt
                      , GETDATE() 
                      FROM tblProduct(NOLOCK) as P
                      INNER JOIN tblCode(NOLOCK) as PC  ON P.ProdCd=PC.Cd and PC.Typ=1053
                      INNER JOIN tblSubProdDetail(NOLOCK) as S ON P.ProdCd=S.ProdCd
                      INNER JOIN tblCode(NOLOCK) as SC ON S.SubProdCd=SC.Cd and SC.Typ=1053
                      WHERE P.ProdDeliveryTypCd=3 and P.ProdStatusCd=1 

              IF(@SelectAll=1)
              BEGIN
                   INSERT INTO #tblContractInterCulturalDataFiltered(
                                      ContractID                        
                                    , ContractProdID                    
                                    , ProdCd                                   
                                    , ProdName    
                                    , ContractSubProdID                                
                                    , SubProdCd                         
                                    , SubProdName                       
                                    , ContractProdStatusCd              
                                    , ContractProdStatusDt              
                                    , ContractSubProdStatusCd    
                                    , ContractSubProdStatusDt    
                                    , ContractProdFeeId
                                    , FeeTypCd    
                                    , RowMatrixParameterCd
                                    , CreatedDate                       
                             )
                             SELECT DISTINCT CP.ContractId,
                             CP.ContractProdId,
                             CP.ProdCd,
                             CPC.Descr,
                             CSP.ContractSubProdID,
                             CSP.SubProdCd,
                             CSPC.Descr,
                             CP.ContractProdStatusCd,
                             CP.ContractProdStatusDt,
                             CSP.ContractSubProdStatusCd,
                             CSP.ContractSubProdStatusDt,
                             CPF.ContractProdFeeId,
                             CPF.FeeTypCd,
                             CPF.RowMatrixParameterCd,
                             Getdate()
                             FROM #tblMasterData(NOLOCK) MD
                             INNER JOIN tblContractProd(NOLOCK) CP ON MD.ProdCd=CP.ProdCd
                             INNER JOIN tblContractSubProd(NOLOCK) CSP ON CP.ContractProdId=CSP.ContractProdId
                             INNER JOIN tblCode(NOLOCK) CPC ON CP.ProdCd=CPC.Cd and CPC.Typ=1053
                             INNER JOIN tblCode(NOLOCK) CSPC ON CSP.SubProdCd=CSPC.Cd and CSPC.Typ=1053
                             INNER JOIN  tblContractProdFee(NOLOCK) CPF ON CP.ContractProdID=CPF.ContractProdId AND CSP.ContractSubProdID=CPF.ContractSubProdId 
                             WHERE ContractId=@ContractId AND CP.ContractProdStatusCd=1 AND CSP.ContractSubProdStatusCd=1 
                             AND CPF.ContractProdFeeTypStatusCd=1 AND CP.StandardProdIndCd=1 AND CSP.StdSubProdIndCd=1
              END
              ELSE
              BEGIN
                      IF(@SubProductList IS NOT NULL)
                      BEGIN
                      INSERT INTO #tblContractInterCulturalDataFiltered(
                                      ContractID                        
                                    , ContractProdID                    
                                    , ProdCd                                   
                                    , ProdName    
                                    , ContractSubProdID                                
                                    , SubProdCd                         
                                    , SubProdName                       
                                    , ContractProdStatusCd              
                                    , ContractProdStatusDt              
                                    , ContractSubProdStatusCd    
                                    , ContractSubProdStatusDt
                                    , ContractProdFeeId   
                                    , FeeTypCd    
                                    , RowMatrixParameterCd
                                    , CreatedDate                       
                             )
                             SELECT DISTINCT CP.ContractId,
                             CP.ContractProdId,
                             CP.ProdCd,
                             CPC.Descr,
                             CSP.ContractSubProdID,
                             CSP.SubProdCd,
                             CSPC.Descr,
                             CP.ContractProdStatusCd,
                             CP.ContractProdStatusDt,
                             CSP.ContractSubProdStatusCd,
                             CSP.ContractSubProdStatusDt,
                             CPF.ContractProdFeeId,
                             CPF.FeeTypCd,
                             CPF.RowMatrixParameterCd,
                             Getdate()
                             FROM #tblMasterData(NOLOCK) MD
                             INNER JOIN tblContractProd(NOLOCK) CP ON MD.ProdCd=CP.ProdCd
                             INNER JOIN tblContractSubProd(NOLOCK) CSP ON CP.ContractProdId=CSP.ContractProdId
                             INNER JOIN tblCode(NOLOCK) CPC ON CP.ProdCd=CPC.Cd and CPC.Typ=1053
                             INNER JOIN tblCode(NOLOCK) CSPC ON CSP.SubProdCd=CSPC.Cd and CSPC.Typ=1053
                             INNER JOIN tblContractProdFee(NOLOCK) CPF ON CP.ContractProdID=CPF.ContractProdId AND CSP.ContractSubProdID=CPF.ContractSubProdId
                             INNER JOIN #tblSelContractSubProds(NOLOCK) SCSP ON CSP.ContractSubProdId=SCSP.ContractSubProdId
                             WHERE ContractId=@ContractId AND CP.ContractProdStatusCd=1 AND CSP.ContractSubProdStatusCd=1 
                             AND CPF.ContractProdFeeTypStatusCd=1 AND CP.StandardProdIndCd=1 AND CSP.StdSubProdIndCd=1
                      END
              END

              INSERT INTO #tblContractInterCulturalDataFilteredUpdate(
                        ContractID                        
                      , ContractProdID                    
                      , ProdCd                                   
                     , ProdName            
                      , ContractSubProdID                        
                      , SubProdCd                         
                      , SubProdName                       
                      , ContractProdStatusCd              
                      , ContractProdStatusDt              
                      , ContractSubProdStatusCd    
                      , ContractSubProdStatusDt    
                      , FeeTypCd
                      , FeeDeterminantCd
                      , RowMatrixParameterCd
                      , ProdFeeCurrCd
                      , RowParameterValueCd
                      , ColumnParameterValueCd
                      , ProdFeePct
                      , RangeFromDayCnt
                      , RangeToDayCnt
                      , ProdFeeLevelMinAmt
                      , ProdFeeLevelMaxAmt
                      , ProdFeeAmt
                      , ContractProdFeeId
                      , CreatedDate                       
              )
              SELECT DISTINCT CICF.ContractId,
              CICF.ContractProdId,
              CICF.ProdCd,
              CICF.ProdName,
              CICF.ContractSubProdID,
              CICF.SubProdCd,
              CICF.SubProdName,
              CICF.ContractProdStatusCd,
              CICF.ContractProdStatusDt,
              CICF.ContractSubProdStatusCd,
              CICF.ContractSubProdStatusDt,
              CPFM.FeeTypCd,
              CPFM.FeeDeterminantCd,
              CPFM.RowMatrixParameterCd,
              CPFM.ProdFeeCurrCd,
              CPFM.RowParameterValueCd,
              CPFM.ColumnParameterValueCd,
              CPFM.ProdFeePct,
              CPFM.RangeFromDayCnt,
              CPFM.RangeToDayCnt,
              CPFM.ProdFeeLevelMinAmt,
              CPFM.ProdFeeLevelMaxAmt,
              CPFM.ProdFeeAmt,
              CICF.ContractProdFeeId,
              Getdate()
              FROM #tblContractInterCulturalDataFiltered(NOLOCK) CICF
              INNER JOIN tblContractProdFeeMatrix(NOLOCK) CPFM ON CICF.ProdCd=CPFM.FeeMatrixProdCd AND CICF.SubProdCd=CPFM.FeeMatrixSubProdCd AND CICF.FeeTypCd=CPFM.FeeTypCd
              INNER JOIN tblContractProdFeeMatrixVersion(NOLOCK) CPFMV ON CPFM.ContractProdFeeMatrixVersionId=CPFMV.ContractProdFeeMatrixVersionId 
              INNER JOIN tblContractProdFee(NOLOCK) CP ON CICF.ContractProdID=CP.ContractProdId AND CICF.ContractSubProdID=CP.ContractSubProdId AND CICF.ContractProdFeeId=CP.ContractProdFeeId
              AND CPFM.FeeDeterminantCd=CP.FeeDeterminantCd AND CPFM.RowMatrixParameterCd =CP.RowMatrixParameterCd
              WHERE CICF.ContractId=@ContractId AND CPFMV.ProdFeeMatrixVerNo=@VersionNo AND CICF.ContractProdStatusCd=1 AND CICF.ContractSubProdStatusCd=1 AND CP.ContractProdFeeTypStatusCd=1
              AND CPFM.RowMatrixParameterCd IN(6,4)
              AND EXISTS(SELECT PFM.ContractProdFeeId from tblProdFeeMatrix(NOLOCK)PFM WHERE CICF.ContractProdFeeId=PFM.ContractProdFeeId)

              IF EXISTS(Select 1 From #tblContractInterCulturalDataFilteredUpdate(NOLOCK))
              BEGIN
                      DELETE PFM
                      FROM tblProdFeeMatrix(NOLOCK) PFM
                      INNER JOIN #tblContractInterCulturalDataFilteredUpdate(NOLOCK) TIFU ON TIFU.ContractProdFeeId=PFM.ContractProdFeeId
              END

              INSERT INTO #tblContractInterCulturalDataFilteredInsert(
                        ContractID                        
                      , ContractProdID                    
                      , ProdCd                                   
                      , ProdName            
                      , ContractSubProdID                        
                      , SubProdCd                         
                      , SubProdName                       
                      , ContractProdStatusCd              
                      , ContractProdStatusDt              
                      , ContractSubProdStatusCd    
                      , ContractSubProdStatusDt    
                      , FeeTypCd
                      , FeeDeterminantCd
                      , RowMatrixParameterCd
                      , ProdFeeCurrCd
                      , RowParameterValueCd
                      , ColumnParameterValueCd
                      , ProdFeePct
                      , RangeFromDayCnt
                      , RangeToDayCnt
                      , ProdFeeLevelMinAmt
                      , ProdFeeLevelMaxAmt
                      , ProdFeeAmt
                      , ContractProdFeeId
                      , CreatedDate                       
              )
              SELECT DISTINCT CICF.ContractId,
              CICF.ContractProdId,
              CICF.ProdCd,
              CICF.ProdName,
              CICF.ContractSubProdID,
              CICF.SubProdCd,
              CICF.SubProdName,
              CICF.ContractProdStatusCd,
              CICF.ContractProdStatusDt,
              CICF.ContractSubProdStatusCd,
              CICF.ContractSubProdStatusDt,
              CPFM.FeeTypCd,
              CPFM.FeeDeterminantCd,
              CPFM.RowMatrixParameterCd,
              CPFM.ProdFeeCurrCd,
              CPFM.RowParameterValueCd,
              CPFM.ColumnParameterValueCd,
              CPFM.ProdFeePct,
              CPFM.RangeFromDayCnt,
              CPFM.RangeToDayCnt,
              CPFM.ProdFeeLevelMinAmt,
              CPFM.ProdFeeLevelMaxAmt,
              CPFM.ProdFeeAmt,
              CICF.ContractProdFeeId,
              Getdate()
              FROM #tblContractInterCulturalDataFiltered(NOLOCK) CICF
              INNER JOIN tblContractProdFeeMatrix(NOLOCK) CPFM ON CICF.ProdCd=CPFM.FeeMatrixProdCd AND CICF.SubProdCd=CPFM.FeeMatrixSubProdCd AND CICF.FeeTypCd=CPFM.FeeTypCd
              INNER JOIN tblContractProdFeeMatrixVersion(NOLOCK) CPFMV ON CPFM.ContractProdFeeMatrixVersionId=CPFMV.ContractProdFeeMatrixVersionId 
              INNER JOIN tblContractProdFee(NOLOCK) CP ON CICF.ContractProdID=CP.ContractProdId AND CICF.ContractSubProdID=CP.ContractSubProdId AND CICF.ContractProdFeeId=CP.ContractProdFeeId
              AND CPFM.FeeDeterminantCd=CP.FeeDeterminantCd AND CPFM.RowMatrixParameterCd =CP.RowMatrixParameterCd
              WHERE CICF.ContractId=@ContractId AND CPFMV.ProdFeeMatrixVerNo=@VersionNo AND CICF.ContractProdStatusCd=1 AND CICF.ContractSubProdStatusCd=1 AND CP.ContractProdFeeTypStatusCd=1 
              AND CPFM.RowMatrixParameterCd IN(6,4)

              IF EXISTS(Select 1 From #tblContractInterCulturalDataFilteredInsert(NOLOCK))
              BEGIN
                             INSERT INTO [dbo].[tblProdFeeMatrix] 
                                    ([ContractProdFeeId]
                                    ,[ContractBundleFeeId]
                                    ,[ContractProdBundleAncilFeeId]
                                    ,[RowParameterValueCd]
                                    ,[ColumnParameterValueCd]
                                    ,[ProdFeeAmt]
                                    ,[ProdFeeCurrCd]
                                    ,[ProdFeePct]
                                    ,[RangeFromDayCnt]
                                    ,[RangeToDayCnt]
                                    ,[RangeFromPct]
                                    ,[RangeToPct]
                                    ,[RangeFromAmt]
                                    ,[RangeToAmt]
                                    ,[ProdFeeLevelMinAmt]
                                    ,[ProdFeeLevelMaxAmt]
                                    ,[FeeNotApplyIndCd]
                                    ,[CreateId]
                                    ,[CreateDt]
                                    ,[UpdateId]
                                    ,[UpdateDt]
                                    ,[FunctionId])

                                    SELECT DISTINCT
                                    TIFI.ContractProdFeeID,
                                    0,
                                    0,
                                    TIFI.RowParameterValueCd,
                                    TIFI.ColumnParameterValueCd,
                                    TIFI.ProdFeeAmt,
                                    TIFI.ProdFeeCurrCd,
                                    TIFI.ProdFeePct,
                                    TIFI.RangeFromDayCnt,
                                    TIFI.RangeToDayCnt,
                                    0,
                                    0,
                                    0,
                                    0,
                                    TIFI.ProdFeeLevelMinAmt,
                                    TIFI.ProdFeeLevelMaxAmt,
                                    0,
                                    @UserId,
                                    GETDATE(),
                                    @UserId,
                                    GETDATE(),
                                    @FunctionId
                                    FROM #tblContractInterCulturalDataFilteredInsert(NOLOCK) TIFI
           END

              IF(@ConvRateFlag=1)
              BEGIN
                      EXEC [ASSET_DB].[dbo].[prcCalculateRates] @UserId,@ContractId,@SelectAll,@SubProductList,@FeesOverrideFlg
              END

              COMMIT TRANSACTION

              SELECT @RetVal=0
              GOTO CLEARTEMPTABLES
              GOTO DONE

           END TRY  

              BEGIN CATCH  
                             SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))  
                                           ,@RetVal = 200 
                             GOTO CLEARTEMPTABLES
                             GOTO ERROR  
              END CATCH    


        ERROR:  
              ROLLBACK TRANSACTION
              RAISERROR(@ErrMsg,16,1 ) 

       CLEARTEMPTABLES:

              BEGIN TRY
                          IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblMasterData]') )
                             BEGIN
                               TRUNCATE TABLE  #tblMasterData
                               DROP TABLE  #tblMasterData
                             END
                             IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFiltered]') )
                             BEGIN
                                    TRUNCATE TABLE  #tblContractInterCulturalDataFiltered
                                    DROP TABLE  #tblContractInterCulturalDataFiltered
                             END
                             IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFilteredInsert]') )
                             BEGIN
                                    TRUNCATE TABLE  #tblContractInterCulturalDataFilteredInsert
                                    DROP TABLE  #tblContractInterCulturalDataFilteredInsert
                             END
                             IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFilteredUpdate]') )
                             BEGIN
                                    TRUNCATE TABLE  #tblContractInterCulturalDataFilteredUpdate
                                    DROP TABLE  #tblContractInterCulturalDataFilteredUpdate
                             END
                             IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblSelContractSubProds]') )
                             BEGIN
                                    TRUNCATE TABLE  #tblSelContractSubProds
                                    DROP TABLE  #tblSelContractSubProds
                             END
              END TRY
              BEGIN CATCH
                      SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))  
                                                  ,@RetVal = 201
                      GOTO ERROR  
              END CATCH


        DONE:  

              SET NOCOUNT OFF  
              Return (@RetVal)    
END
1
sqlsql-servertsqltransactions
Scattering NOLOCK hints in your code inside of a transaction is usually a sign of blind pattern usage. Highly doubtful that this hint is appropriate - especially when the queries are used to insert/update actual rows. The many uses of DISTINCT are also highly suspicious. Deleting from a table using the nolock hint is pointless, as is applying nolock hints to local temp tables. Truncating a table before dropping it makes no sense. Use of goto. <Big heavy sigh>. Get help! - SMor

1 Answers

0
votes

The fact that lead to this error is that the BEGIN TRANSACTION executed, but neither COMMIT or ROLLBACK did.

The usual reason a stored procedure with a try-transaction block returns that message is the error: table (or other object) not found. This is NOT caught in the try-catch block.

Check the object names exist on runtime.

Also, make sure to run a ROLLBACK manually on the session that spawned this message, if you still haven't fixed the proc.

Documentation:

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY...CATCH construct:

(........)

Object name resolution errors

As you can see, there are also other errors not caught which may be the problem. Check out the possibilities. Many of these error, including the object name resolution, can be caught if you put the procedure CALL inside a try-catch block.

begin try
exec [dbo].[prcInsDataFeesForVersion] ..............
end try
begin catch
SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))  
                                                  ,@RetVal = 201
end catch

This might not sound handy, but at least you can use it to find the error out.