Why is UiPath Upgrade 2022.10 failing due to the " create unique index" error?
Issue Description:
UiPath Upgrade 2022.10 is failing due to the " create unique index" error
Resolution:
1. Orchestrator upgrade from 2020.10 to 22.10 failure observed with below error
2. "CREATE UNIQUE INDEX statement terminated due to a duplicate key in 'dbo.vwQueueReferences' and 'IX_vwQueueReferences'. Duplicate key value: (440, 7397031|8ff0b49b-3494-403c-b238-246be6ccfbeb)."
3. Table under the view section checked, no duplicate entries found.
4. Identified that 'dbo.vwQueueReferences' retrieves data reference from 'dbo.QueueItems' table, filtered by Id and date columns.
5. Discovered and deleted duplicate column, but installer still failed due to a different duplicate value.
6. Executed below query to find all duplicates in 'dbo.QueueItems' table, over 5000 duplicate values were found.
7. Ensured a database backup before running below SQL query to remove duplicates.
8. After removing duplicates and rerunning the installer, the upgrade was successful.
SQL Query for finding Duplicates in the dbo.QueueItems
SELECT QueueDefinitionId, Reference, COUNT(*)
FROM [dbo].[QueueItems]
GROUP BY QueueDefinitionId, Reference
HAVING COUNT(*) > 1;
SQL Query for deleting Duplicates in the dbo.QueueItems
WITH CTE AS (
SELECT QueueDefinitionId, Reference,
ROW_NUMBER() OVER (PARTITION BY QueueDefinitionId, Reference ORDER BY (SELECT NULL)) AS RowNum
FROM [dbo].[QueueItems]
)
DELETE FROM CTE WHERE RowNum > 1;