Error CREATE UNIQUE INDEX ON DBO.PROJHIERARCHYSORTING Dynamics 365 for finance and Operations
I faced this problem while restoring staging database from 8.0 to latest version.
Microsoft did not provide any solution to extended index. You can add new indexes on table extension, but did remove or modified on existing indexes.
Severity Code Description Project File Line Suppression StateError CREATE UNIQUE INDEX I_17901NODEREFERENCE ON DBO.PROJHIERARCHYSORTING(PARTITION,DATAAREAID,TYPE,REFID,REFNODEID); —> System.Data.SqlClient.SqlException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.PROJHIERARCHYSORTING’ and the index name ‘I_17901NODEREFERENCE’. The duplicate key value is (5637144576, atd, 0, , 0). 0
One solution I found that DBO.PROJHIERARCHYSORTING is have some kind of sorting data, And When we open Work Break down structure
this table is auto populate some kind of internal usage for sorting.
So I truncate the DBO.PROJHIERARCHYSORTING table and as result Database Synchronization successfully completed.
And opening WBS of existing table, I found data at SQL server level.
There is no solution to extend table index on table. So only solution to truncate the table.