One of the most common headaches experienced when you upgrade Dynamics AX is unique index errors, so I wrote a little sql script to fix those. This commonly experienced with date effective tables or tracking tables in general. In fact, if you upgrade the AX database from CU6 to CU7 on the sample Contoso image, you will experience this. More particularly, you will get these types of errors when you try to synchronize:

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)

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 1^st 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

Or if you want to see a screenshot: