KB206780: "CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.DSSMDXXXXXXX' and the index name 'PK_XXXXXXX'" error when upgrading a MicroStrategy metadata in SQL Server database.

SUMMARY
This technical note outlines an issue customers may experience when trying to upgrade a metadata in SQL Server database. Upon upgrading a metadata, an error may occur relating to keys in the database.
 
SYMPTOM
When trying to upgrade a metadata repository in SQL Server to the most recent version, an error may be thrown during the process:

"CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.DSSMDXXXXXXX' and the index name 'PK_XXXXXXX'"

After receiving this error, the metadata becomes unusable. The error message can change for the object name and index name depending on the metadata.
The error message above references a metadata repository hosted in SQL Server, although this can occur in other databases as well that utilize primary key constraints.
Neither MD Doctor nor ScanMD will resolve this issue.
 

CAUSE

This error message is indicative that there are duplicate records in the metadata for one or more of the tables in the metadata repository. Duplicate entries can occur in a database table if the primary key constraints are removed.
There are only two scenarios where Strategy will ever remove primary key constraints from a SQL Server metadata, and that is upon first creating the metadata shell and upgrading the metadata. In both instances, the primary key constraints are immediately recreated.
 

ACTION

To troubleshoot this issue, talk to your DBA to gather more information about when these objects were removed and how, and possibly restore an earlier backup of your metadata when the primary key constraints still existed. At that point, run Project Merge to move over the projects from the corrupted metadata to the working metadata.
If you are unable to obtain a copy of the metadata that has these primary key constraints, please contact Strategy Technical Support with a copy of your metadata BEFORE the upgrade was attempted and reference this technical note.
 

DISCLAIMER

Note: Removing the primary key constraints from a Strategy metadata is unsupported and Strategy Technical Support cannot guarantee a proper fix for this issue, even if a backup of the metadata is provided.