The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction?

Question

Hello Every One The Following Error Comes To Me Suddenly When I Changed Simple And Few Thing On My Stored Procedure Code And It Was Working Great And If I Cleared The Updated Code It Works Fine Again So I Do Not Know What Is The Reason And Here Is The Error "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction"

Thanks In Advance

ALTER Procedure [dbo].[xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx]
    @English Bit = 0 , 
    @ID BigInt  =  NULL Output  , 
    @Company_ID SmallInt  =  NULL , 
    @Cust_ID NVarChar (20) =  NULL , 
    @Cust_Cat NVarChar (10) =  NULL , 
    @Debit_Limit Decimal (18,3) =  NULL , 
    @From_Date NVarChar (19) =  NULL , 
    @To_Date NVarChar (19) =  NULL , 
    @User_ID NVarChar(50) = NULL 
As
BEGIN
  Create Table #Errors (ErrorNumber int Not Null, ErrorValue nvarchar(300) Collate Arabic_CI_AS Null);
  Begin Tran trn_INV_CustDebLim_setupInsert;
  Begin Try
    Insert Into INV_Cust_Debit_Limit_setup
    (  Company_ID ,  Cust_ID ,  Cust_Cat ,  Debit_Limit ,  From_Date ,  To_Date  ) 
    Values
    ( @Company_ID , @Cust_ID , @Cust_Cat , @Debit_Limit , @From_Date , @To_Date  ) 
    set @ID = @@IDENTITY
    -- From Here Is New Part --
    declare @str nvarchar(50)
    IF(@Cust_ID IS NOT NULL)
        set @str = 'xxxxxxxxxxxxxxxxxxxxxxxxxxx ' + @Cust_ID 
    IF(@Cust_Cat IS NOT NULL)
        set @str += 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx ' + @Cust_Cat
    set @str += ' xxxxx' +@Debit_Limit + '  xxxx xx' +@From_Date
    IF(@English = 1)
        BEGIN
            IF(@Cust_ID IS NOT NULL)
                set @str = 'Credit Limit Added On Customer ' + @Cust_ID
            IF(@Cust_Cat IS NOT NULL)
                set @str += 'Credit Limit Added On Customer Category ' + @Cust_Cat
            set @str = ' With Value ' +@Debit_Limit + ' From Date ' +@From_Date
        END
    exec usp_GLApplicationAudit @Company_ID , NULL , 10 , @User_ID , @str ,@ID ,10, NULL , NULL ,NULL ,NULL,NULL,'SAl'
    -- To Here Is New Part --
  End Try
  Begin Catch
      Insert #Errors Values(1002,ERROR_MESSAGE());      
      Goto Finished;
  End Catch
-- Return Error Records
Finished:
    -- retrieve Errors and commit/Rollbak Trans.
    If (Select count(E.ErrorNumber)
            From #Errors E Left Join GVT_Errors G
                On E.ErrorNumber = G.Err_Number
            Where G.Err_Type=0 ) > 0
        Begin
            -- The Following are Errors
            Select E.ErrorNumber As [Err_No], G.MessageA + ': ' + E.ErrorValue As [Err_Desc], Err_Source, dbo.fn_GetItemDescription('Err_Type',Cast(Err_Type As nvarchar), null, null, null, null, 0) As Err_Type_Desc, Err_Type, SeverityLevel As [Severity], CategoryID
              From #Errors E Left Join GVT_Errors G 
                On E.ErrorNumber = G.Err_Number;
            Rollback Tran trn_INV_CustDebLim_setupInsert;
        End
    Else
        Begin
            -- The Following Not Errors They are Warnings or Information
            Select E.ErrorNumber As [Err_No], G.MessageA + ': ' + E.ErrorValue As [Err_Desc], Err_Source, dbo.fn_GetItemDescription('Err_Type',Cast(Err_Type As nvarchar), null, null, null, null, 0) As Err_Type_Desc, Err_Type, SeverityLevel As [Severity], CategoryID
              From #Errors E Left Join GVT_Errors G 
                On E.ErrorNumber = G.Err_Number;
            Commit Tran trn_INV_CustDebLim_setupInsert;
        End
    DROP Table #Errors;

END

Answer 1

In the CATCH code you must check the state of XACT_STATE() and act accordingly. For a procedure template that handles transactions and try/catch blocks correctly see Exception Handling and Nested Transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end