none
The current transaction cannot be committed RRS feed

  • Question

  • I have a tsql script made up of multiple batches several thousand lines long.

    Somewhere, in the middle of the tsql script, I have the 2 lines below.  The error says the following:

    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    System.Data.SqlClient.SqlException (0x80131904): The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

    Now, if I am to remove the two lines, below, everything works as part of a transaction.  I checked for hidden characters. Couldn't find anything.  I, also, tried to wrap it in sp_executesql, but no luck.

    Note, the issue happens only when I wrap the sql in transaction like so servConnSBL.BeginTransaction();.  If I take out the transaction everything works.

    Any help is appreciated.


    EXEC SP_CHL_Recreate_ChgTyp_Calcs
    GO

    Thursday, November 28, 2019 11:51 PM

All replies

  • EXEC SP_CHL_Recreate_ChgTyp_Calcs

    And what do your script before and what do that stored procedure do; we can't guess that?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, November 29, 2019 5:24 AM
  • Thanks for taking time looking into this.

    Below, is the sproc being.  To make life easier, I have only single statement executing the sproc, below and it produces the issue mentioned before in this post.

    CREATE PROCEDURE SP_CHL_Recreate_ChgTyp_Calcs
    AS
    SET XACT_ABORT ON

    begin try drop table #RecalcHelper end try begin catch end catch
    begin try drop table #FirstLast end try begin catch end catch

    --create table #ChangeScopes (ChangeScope Enum3)
    --insert #ChangeScopes (ChangeScope)
    --select ChangeScope from ChangeTypeBits group by ChangeScope 

    declare @DBVersion ShortString
    select top 1
    @DBVersion = dbo.fn_dsmpnet_dbversion()

    create table #RecalcHelper(
    ResSQL nvarchar(max),
    JoinSQL nvarchar(max),

    ID bigint identity(1, 1) primary key NOT NULL,
    ChangeScope Enum3,
    BitMask bigint,
    ObjectType String,
    HistoryName String,
    SpecialInt int NULL,
    ClassIntSQL nvarchar(max),
    ColumnName String,
    Alias ShortString,
    OperType Enum,
    ChangeType Enum3,

    FirstHistory bit default 0,

    FirstBitMask bit default 0,
    LastBitMask bit default 0,

    FirstClass bit default 0,
    LastClass bit default 0,

    FirstOperTypeU bit default 0,
    LastOperTypeU bit default 0
    )
    insert into #RecalcHelper(ChangeScope, BitMask, ObjectType,
    HistoryName, SpecialInt, ClassIntSQL, ColumnName, Alias, OperType, ChangeType)
    select
    CTB.ChangeScope,
    CTB.BitMask,
    CTB.ObjectType,
    ISNULL(CLH.HistoryName, 'ChangeLogHelper') as HistoryName,
    CTB.SpecialInt,
    CLH.ClassIntSQL,
    DBVS.COLUMN_NAME as ColumnName,
    ISNULL(JAlias.Alias, 'SCO') as Alias,
    CTB.Operation,
    CTB.ChangeType
    from
    ChangeTypeBits CTB with(nolock)
    INNER JOIN
    ChangeLogHistories CLH with(nolock)
    on (CLH.ObjectType = CTB.ObjectType)
    INNER JOIN
    DBVersionStructure DBVS with(nolock)
    on (DBVS.TABLE_NAME = CTB.ObjectType) and (@DBVersion = DBVS.DBVersion)
    left join
    (
    select 'Household' as ObjectType,'HOH' as Alias
    union select 'PupEdEn', 'SSE'
    union select 'Pup', 'STP'
    union select 'PupCont', 'STC'
    union select 'Accounts', 'ACC'
    ) JAlias
    on (JAlias.ObjectType = CTB.ObjectType)
    where
    CTB.ChangeType <> 'HID' and
    (ChangeScope != 'STP' OR JAlias.Alias IS NOT NULL) and

    (dbo.FN_CHL_IsValidChangeType(CTB.ChangeScope,
    CTB.SpecialInt, CTB.ObjectType, DBVS.COLUMN_NAME,
    CTB.ChangeType, CTB.Operation) = 1)
    order by
    CTB.ChangeScope, CTB.BitMask, CTB.ObjectType, CLH.HistoryName, CTB.SpecialInt, CLH.ClassIntSQL,
    CTB.Operation, DBVS.COLUMN_NAME

    select
    case when GROUPING(ObjectType) = 1 then min(ID) else NULL end as FirstBitMaskID,
    case when GROUPING(ObjectType) = 1 then max(ID) else NULL end as LastBitMaskID,
    case when GROUPING(OperType) = 1 then min(ID) else NULL end as FirstSubclassID,
    case when GROUPING(OperType) = 1 then max(ID) else NULL end as LastSubclassID,
    min(RCH_OperType.ID) as FirstOperTypeID,
    max(RCH_OperType.ID) as LastOperTypeID,

    cast(NULL as bigint) as FirstHistoryID
    into #FirstLast
    from
    #RecalcHelper RCH_OperType with(nolock)
    group by
    RCH_OperType.ChangeScope,

    RCH_OperType.BitMask,
    -- Subclass 3 fields:
    RCH_OperType.ObjectType,
    RCH_OperType.ClassIntSQL,
    RCH_OperType.SpecialInt,

    RCH_OperType.OperType
    with rollup

    -- 1.2. Insert for FirstHistory
    insert into #FirstLast(FirstHistoryID)
    select min(ID) as FirstHistoryID
    from #RecalcHelper RCH with(nolock)
    group by
    ChangeScope, HistoryName

    -- 2. Calculate First/Last bits
    update RCH set
    FirstBitMask = case when ID in (select FirstBitMaskID from #FirstLast) then 1 else 0 end,
    FirstClass = case when
    (ISNULL(RCH.ClassIntSQL, '') <> '') AND (RCH.SpecialInt IS NOT NULL) AND
    (ID in (select FirstSubClassID from #FirstLast)) then 1 else 0 end,
    FirstOperTypeU = case when (OperType = 'U') and (RCH. ID in (select FirstOperTypeID from #FirstLast)) then 1 else 0 end,

    FirstHistory = case when ID in (select FirstHistoryID from #FirstLast) then 1 else 0 end,

    LastBitMask = case when ID in (select LastBitMaskID from #FirstLast) then 1 else 0 end,
    LastClass = case when
    (ISNULL(RCH.ClassIntSQL, '') <> '') AND (RCH.SpecialInt IS NOT NULL) AND
    (ID in (select LastSubClassID from #FirstLast)) then 1 else 0 end,
    LastOperTypeU = case when (OperType = 'U') and (RCH. ID in (select LastOperTypeID from #FirstLast)) then 1 else 0 end
    from
    #RecalcHelper RCH with(rowlock)

    --dbcc dropcleanbuffers

    -- 5.1. Column Comparision (XML/HST)
    declare @UExpr nvarchar(max), @IExpr nvarchar(max), @DExpr nvarchar(max)

    set @UExpr = '(ISNULL(cast(<ALIAS>_OLD.<COL> as nvarchar(max)), ''_NULL_'') !=
    ISNULL(cast(<ALIAS>_NEW.<COL> as nvarchar(max)), ''_NULL_''))'
    set @IExpr = '(<ALIAS>_OLD.RefID IS NULL AND <ALIAS>_NEW.RefID IS NOT NULL)'
    set @DExpr = '(<ALIAS>_OLD.RefID IS NOT NULL AND <ALIAS>_NEW.OperType = ''D'')'

    -- 5.1.1. Column Comparision (HST)
    update RCH set
    ResSQL = replace(replace(
    case OperType when 'I' then @IExpr when 'D' then @DExpr else @UExpr end,
    '<ALIAS>', Alias), '<COL>', ColumnName)
    from #RecalcHelper RCH with(rowlock)
    where HistoryName != 'ChangeLogHelper'

    set @UExpr = '(ISNULL(<ALIAS>_OLD.<COL>, ''_NULL_'') != ISNULL(<ALIAS>_NEW.<COL>, ''_NULL_''))'
    set @UExpr = replace(@UExpr, '<COL>', 'XMLChange.value(''(*/<COL>)[1]'', ''nvarchar(max)'')')
    set @IExpr = replace(@IExpr, '<COL>', 'XMLChange.value(''(*/<COL>)[1]'', ''nvarchar(max)'')')
    set @DExpr = replace(@DExpr, '<COL>', 'XMLChange.value(''(*/<COL>)[1]'', ''nvarchar(max)'')')

    -- 5.1.2. Column Comparision (XML)
    update RCH set
    ResSQL = replace(replace(
    case OperType when 'I' then @IExpr when 'D' then @DExpr else @UExpr end,
    '<ALIAS>', Alias), '<COL>', ColumnName)
    from #RecalcHelper RCH with(rowlock)
    where HistoryName = 'ChangeLogHelper'

    -- 5.2. per OperType = 'U'
    update RCH set
    ResSQL = replace('(<ALIAS>_OLD.RefID IS NOT NULL AND <ALIAS>_NEW.OperType <> ''D'')',
    '<ALIAS>', Alias) + ' AND (
    ' + ResSQL
    from #RecalcHelper RCH with(rowlock) where FirstOperTypeU = 1

    update RCH set
    ResSQL = ResSQL + '
    )'
    from #RecalcHelper RCH with(rowlock) where LastOperTypeU = 1

    -- 5.3. per Class_SpecialInt
    update RCH set ResSQL = '((' + cast(SpecialInt as varchar(50)) + ' = ' + 
    replace(ClassIntSQL,'<ALIAS>',
    Alias + case when RCH.OperType = 'D' then '_OLD' else '_NEW' end)
    + ') AND (
    ' + ResSQL

    from #RecalcHelper RCH with(rowlock) where FirstClass = 1

    update RCH set ResSQL = ResSQL + '))'
    from #RecalcHelper RCH with(rowlock) where LastClass = 1

    -- 5.4. per BitMask
    update RCH set ResSQL = '|
    case when ' + ResSQL
    from #RecalcHelper RCH with(rowlock) where FirstBitMask = 1

    update RCH set ResSQL = ResSQL + '
    then ' + cast(RCH.BitMask as varchar(50)) + ' -- ' + RCH.ChangeType + '
    else 0 end '
    from #RecalcHelper RCH with(rowlock) where LastBitMask = 1

    -- 5. per ChangeScope
    declare @ChangeScope ShortString, @PrevChangeScope ShortString
    declare @SQL nvarchar(max), @Joins nvarchar(max), @DropSQL nvarchar(max), @SPName nvarchar(max)

    set @PrevChangeScope = ''

    while 1 = 1
    begin
    set @ChangeScope = NULL
    select top 1 @ChangeScope = ChangeScope
    from
    #RecalcHelper RCH with(nolock)
    where
    RCH.ChangeScope > @PrevChangeScope
    order by RCH.ChangeScope

    if @ChangeScope IS NULL
    begin
    break;
    end;
    set @PrevChangeScope = @ChangeScope

    set @SPName = 'SP_CHL_Recalc_' + @ChangeScope + '_ChangeTypes'
    set @DropSQL = '
    if exists (select * from sys.objects where name = ''' + @SPName + ''')
    drop proc ' + @SPName + '
    '
    select @SQL = '
    create procedure ' + @SPName + '
    as
    update FTM
    set
    ChangeTypeMask = 0 ', @Joins = '
    from
    #FTemp FTM with(rowlock) '

    -- 2. Prepare Joins
    select
    @Joins = @Joins + '
    left join
    ' + HistoryName + ' ' + Alias + '_OLD with(nolock)
    on ' + Alias + '_OLD.History_Id = FTM.' + Alias + '_OLD_HID'
    + case when HistoryName = 'ChangeLogHelper' then
    ' and FTM.ObjectType = ' + Alias + '_OLD.ObjectType' else '' end + '
    left join
    ' + HistoryName + ' ' + Alias + '_NEW with(nolock)
    on ' + Alias + '_NEW.History_Id = FTM.' + Alias + '_NEW_HID'
    + case when HistoryName = 'ChangeLogHelper' then
    ' and FTM.ObjectType = ' + Alias + '_NEW.ObjectType' else '' end

    from #RecalcHelper RCH with(rowlock)
    where FirstHistory = 1 and ChangeScope = @ChangeScope

    -- 3. Prepare SQL
    select
    @SQL = @SQL + ResSQL
    from
    #RecalcHelper RCH with(nolock)
    where
    RCH.ChangeScope = @ChangeScope

    set @SQL = replace(@SQL, ')(', ') OR
    (') + @Joins + '
    where (FTM.ChangeScope = ''' + @ChangeScope + ''')
    '
    --print @DropSQL
    exec (@DropSQL)

    --exec SP_DEV_LongPrint @string = @SQL
    exec (@SQL)
    end;


    Friday, November 29, 2019 4:50 PM