1

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:

https://technet.microsoft.com/en-us/library/jj733502.aspx

Jeffrey Saelen
  • 23
  • 7

2 Answers2

1

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

FH-Inway
  • 4,432
  • 1
  • 20
  • 37
Jeffrey Saelen
  • 23
  • 7
  • Thank you for the useful post and article , it creates a better view of the problem at hand ! – Jeffrey Saelen May 16 '18 at 09:05
  • If you [accept your answer](https://stackoverflow.com/help/accepted-answer), you can let others know that this question has been answered and likely does not need another answer. – FH-Inway May 16 '18 at 15:34
0

It is as if the kernel itself is trying to add this index

No )

  • Table: AGREEMENTHEADER
  • Index: PURCHNUMBERSEQUENCEIDX
  • RecId of duplicated record is 5637144576

You cannot find this index on a SQL because it is disabled in a Axapta. Open AOT and explore AOT table, index and property. Try to enable this index on your AX2012FP via AOT property.

IF your problem not solved THEN install a new instance of AX2012R3 and explore AOT and compare AOT indexes in your Axapta and new AX2012R3. Modify your axapta before upgrade to eliminate index differences.

mazzy
  • 1,025
  • 1
  • 13
  • 26