I am trying to upgrade my AX2012FP to the R3 version , but during the process I am getting errors on the synchronisation (so right after presync) . One of the errors is the following:
SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.AGREEMENTHEADER' and the index name 'I_4902PURCHNUMBERSEQUENCEIDX'. The duplicate key value is (5637144576, , 0, 4902).
The problem here is that if i check in the AOT and in SQL this index does not exist so I can not temporarily make it non-unique until the effective data upgrade. The table supports inheritance but does not extend from anything. It is as if the kernel itself is trying to add this index. Deleting the records is not an option as I would lose data.
I am following the official guide which tells me to use the data upgrade checklist for an in-place data upgrade:
I have found the cause of this issue, and it was as suspected. I checked the AOT in my pre-upgrade environment and it was not present also.
So whilst searching , i let my client search all the tables on this index, i then found a derived table which uses this index. Then i made it not unique and i can continue the process now.
Appearantly when you create an index on a derived table, the kernell will - at some point - try to add the index on the parent table. This is due to the fact that while tables from a table inheritance appear as separate table objects in the AOT, they are actually (in AX 2012 R2 and higher) a single table in the database. The excellent article Queries to tables with inheritance by Martin Dráb is recommended for further reading.
Edited : i made a presync script for this and will have to tackle the unique indexes problem later during the data upgrade