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 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
— replace this column with the effective date column
— replace this table with the name of the one causing sync errors
Or if you want to see a screenshot: