Getting error while upgrading the AX database from CU6 to CU7

This topic contains 1 reply, has 2 voices, and was last updated by  Locus IT 1 year ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #20645

    Andrew Jenson
    Participant

    I am getting the following error while upgrading the AX database from CU6 to CU7

    Error Synchronize database Cannot execute a data definition language command on (). The SQL database has issued an error.

    Info Synchronize database SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.HCMPOSITIONFORECASTBUDGETACCTLINE’ and the index name ‘I_100439POSITIONFORECASTLEPURPOSEDE60002’. The duplicate key value is (5637144577, 0, 0, 22565423328, Jan 1 1900 12:00AM, Jan 1 1900 12:00AM, 0).

    Info Synchronize database SQL statement: CREATE UNIQUE INDEX I_100439POSITIONFORECASTLEPURPOSEDE60002 ON “DBO”.HCMPOSITIONFORECASTBUDGETACCTLINE (PARTITION,POSITIONFORECASTSCENARIO,BUDGETPURPOSETYPEDETAIL,LEGALENTITY,EFFECTIVEDATE,EXPIRATIONDATE,ISSYSTEMGENERATED)
    Error Synchronize database Cannot execute a data definition language command on ().

    The SQL database has issued an error.

    Info Synchronize database SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.HCMPOSITIONFORECASTLASTMODIFIED’ and the index name ‘I_100445POSITIONFORECASTIDX’. The duplicate key value is (5637144577, 0).

    Info Synchronize database SQL statement: CREATE UNIQUE INDEX I_100445POSITIONFORECASTIDX ON “DBO”.HCMPOSITIONFORECASTLASTMODIFIED (PARTITION,POSITIONFORECASTSCENARIO)
    Error Synchronize database Problems during SQL data dictionary synchronization.
    The operation failed.
    Info Synchronize database Synchronize failed on 2 table(s)

    Can anyone please help me out?

    #20982

    Locus IT
    Participant

    Hello Andrew,

    Background: Date-Effective tables are a big deal. They allow you to track the history of when a record is valid for example. For example, if a customer changes address, you may want to be able to record that a customer lived in Oklahoma from June 2002 till June 2006 and Texas from 2006 till current. You can see why these are so important.

    But to make this happen, fundamental changes needed to be introduced in tables. The problem with this is that AX populates the record dates as January 1st 1900 for records that existed before time recording was introduced. This leads to errors as the SQL indexes can’t create because of these duplicate values.

    Solution: since 1900 is just a dummy value, keep on populating the date-effective tables with dummy values for historical data that existed before the real data starts coming in. You just need to tell everyone that anything in 1900 should not be assumed to be a real data year. To do this, I’m going to use a classic sql cursor statement. Here is an example of a script to resolve the errors if you need to copy and paste

    Declare
    @datevalue
    datetime,
    @modifieddatetime datetime,
    @basedate datetime

    SET
    @basedate
    =
    ’01/01/1900′

    Declare
    resolve_axupgrade
    Cursor
    For

    Select
    ModifiedDateTime
    — replace this column with the effective date column

    from
    HCMPositionForecastLastModified
    — replace this table with the name of the one causing sync errors

    for
    update
    of
    ModifiedDateTime

    open
    resolve_axupgrade

    Fetch
    resolve_axupgrade

    into
    @datevalue

    WHILE
    @@FETCH_STATUS
    = 0

    BEGIN

    update
    dbo.HCMPOSITIONFORECASTLASTMODIFIED

    set
    MODIFIEDDATETIME
    =
    @modifieddatetime

    where
    current
    of
    resolve_axupgrade

    SET
    @basedate
    =
    DATEADD(day,1,@basedate)

    SET
    @modifieddatetime
    =
    @basedate

    FETCH
    resolve_axupgrade
    INTO
    @datevalue

    END

    Close
    resolve_axupgrade

    DEALLOCATE
    resolve_axupgrade

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.

Skip to toolbar