Queue Unique Reference Feature Is Not Working

News Knowledge Base
,
1

Why the Unique Reference is allowing duplicates to be created in 2021.10 On-Premise Orchestrator, while using the Add Queue Item activity?

Issue Description

There are queues where the Unique Reference is enabled.

1.png

Note: Once the Queue is created, the Enforce unique references feature can not be changed. It will be allowed only when creating a new Queue, or if no duplicates are present in the respective Queue, it will be allowed from the database.

Example:

  • UPDATE [UiPath].[dbo].[QueueDefinitions]
    SET EnforceUniqueReference = 1 WHERE [Name] = 'REPLACE_WITH_YOUR_QUEUE_NAME';


If duplicates are present in the Queue, an error as below is thrown

Msg 2601, Level 14, State 1, Line 25

Cannot insert duplicate key row in object 'dbo.vwQueueReferences' with unique index 'IX_vwQueueReferences'. The duplicate key value is (4, QueueItemTest).

The statement has been terminated.


While executing the Add Queue Item activity in the automation, the Orchestrator is allowing to add duplicates in our UiPath database, which is not the expected behavior.

2.png

Example:

3.png

Resolution

The below solution can be applied for On-Premise and Automation Suite 2021.10 versions.

Note: Be cautious while accessing or updating the Orchestrator database, as UiPath is not responsible for the data loss due to wrong data manipulations.

Remove the duplicates from the [UiPath].[dbo].[QueueItems] table and enable/ recreate the clustered index IX_vwQueueReferences for the dbo.vwQueueReferences View.

  1. To check whether an index, like IX_vwQueueReferences, is disabled on your view dbo.vwQueueReferences, you can use the following system catalog views:

sys.indexes: Contains a row per index of the object.

sys.objects: Contains a row for every user-defined, schema-scoped object that is created within a database.

Here is the SQL query to check the status:

SELECT 
  o.name AS ObjectName,
  i.name AS IndexName,
  i.type_desc AS IndexType,
  i.is_disabled AS IndexIsDisabled
FROM 
  sys.indexes i
INNER JOIN 
  sys.objects o ON i.object_id = o.object_id
WHERE 
  o.type = 'V' AND  -- View
  o.name = 'vwQueueReferences' AND  -- Replace with your view name
  i.name = 'IX_vwQueueReferences'; -- Replace with your index name

This will return a row with the details of the index, including a column IndexIsDisabled. If IndexIsDisabled value for the row is 1, it means the index is disabled. If it is 0, the index is not disabled. If there are no rows, that means that the IX_vwQueueReferences index is not created at all and you will need to create it manually after you delete the duplicates from the [UiPath].[dbo].[QueueItems] table.

Replace 'vwQueueReferences' and 'IX_vwQueueReferences' with the actual view name and index name.

To enable the indexes for the view, the duplicates need to be deleted from the [UiPath].[dbo].[QueueItems] table, and then you will be able to rebuild.

  1. The below query allows to display the duplicates in the [UiPath].[dbo].[QueueItems] and group them by References.
SELECT [Reference], COUNT(*) FROM [UiPath].[dbo].[QueueItems]
GROUP BY [Reference]
HAVING COUNT(*) > 1;

To display the duplicates for a specific Reference, this query will help:

SELECT [Reference], COUNT(*) FROM [UiPath].[dbo].[QueueItems] WHERE [Reference] = 'REPLACE_WITH_YOUR_REFERENCE_NAME'
GROUP BY [Reference]
HAVING COUNT(*) > 1;

In the below example, there are 3 duplicates for QueueItemTest Reference.

4.png

  1. Before deleting the duplicates from the database, it is recommended to create either a database backup or create a backup for the manipulated database [UiPath].[dbo].[QueueItems] table.
  • The below query will allow to create a new backup table where all the records from the [UiPath].[dbo].[QueueItems] table will be stored. 
SELECT * INTO [UiPath].[dbo].[QueueItemsArchive] FROM [UiPath].[dbo].[QueueItems]

Note: Make sure adequate permissions are present to create a new table in the database and ensure that a table named '[UiPath].[dbo].[QueueItemsArchive]' in the database is not already present, as the 'SELECT INTO' statement would fail in that case.

  • This query will return a single row with three columns: QueueItemsCount, QueueItemsArchiveCount, and ComparisonResult. The ComparisonResult column will contain the text 'Identical' if the counts are the same, and 'Not identical' if they are not.
SELECT (
    SELECT COUNT(*)
    FROM [UiPath].[dbo].[QueueItems]
) AS QueueItemsCount,
(
    SELECT COUNT(*)
    FROM [UiPath].[dbo].[QueueItemsArchive]
) AS QueueItemsArchiveCount,
CASE 
    WHEN (
    SELECT COUNT(*)
    FROM [UiPath].[dbo].[QueueItems]
    ) = (
    SELECT COUNT(*)
    FROM [UiPath].[dbo].[QueueItemsArchive]
    )
    THEN 'Identical'
    ELSE 'Not identical'
END AS ComparisonResult
Examples:

5.png
6.png

  • The below SQL query will take a full backup of a SQL Server database.
-- Replace 'YourOrchestratorDatabaseName' with the actual database name
-- Replace 'YourBackupPath\YourDatabaseName.bak' with the actual backup file path

BACKUP DATABASE YourOrchestratorDatabaseName

TO DISK = ‘YourBackupPath\YourOrchestratorDatabaseName.bak’

WITH FORMAT, INIT, NAME = ‘YourOrchestratorDatabaseName-Full Database Backup’, STATS = 10;

Example:
BACKUP DATABASE UiPath
TO DISK = 'D:\Backups\UiPath.bak'
WITH FORMAT, INIT, NAME = 'UiPath-Full Database Backup', STATS = 10;


In this example, the database named "UiPath" is being backed up to a file named "UiPath.bak" located in "D:\Backups" directory.

The WITH FORMAT option tells SQL Server to create a new media set, and the INIT option tells SQL Server to overwrite any existing backup sets on the disk file.

The STATS = 10 option is used to display a message after every 10 percent processed during the backup operation.

Make sure to run this command with an SQL Server login that has the backup database privilege.

  1. This query will keep the most recent row for each [Reference] based on [CreationTime] and will delete all the older records for each [Reference].

This query first creates a Common Table Expression (CTE) named "cte". The CTE uses the ROW_NUMBER function to assign a unique row number "rn" to each row, partitioned by Reference and ordered by CreationTime in descending order. This means that for each Reference, the row with the greatest CreationTime (i.e., most recent) will have row number 1, the next most recent will have row number 2, and so on.

The DELETE statement then deletes all rows from the CTE (and thus from the original table) where the row number is greater than 1. This effectively keeps only the most recent row for each Reference and deletes others.

Make sure to backup the data before executing the command, just in case any important data gets lost.

WITH cte AS
(
SELECT [Id], 
ROW_NUMBER() OVER(PARTITION BY [Reference] ORDER BY [CreationTime] DESC) AS rn
FROM [UiPath].[dbo].[QueueItems]
)
DELETE FROM cte
WHERE rn > 1

Note: Adapt the above query based on your internal purposes, as you know better which is the condition to keep your records. This query may take a while in case you have a large number of queue items in your Orchestrator database.

  1. If the IX_vwQueueReferences index is not created in the dbo.vwQueueReferences View, then it is required to create it.


Go to the Orchestrator database -> View -> dbo.vwQueueReferences -> Indexes -> right-click -> Nw Index -> Clustered Index... -> In the Index Name provide IX_vwQueueReferences, Make sure that Unique is checked -> Add... -> select QueueDefinitionId and Reference -> OK -> OK

7.png

Note: If tried to create the Index while duplicates are still present in the [UiPath].[dbo].[QueueItems] table, it will not be allowed.

Example:

8.png

Expected results after performing the above:

UiPath.Core.Activities.OrchestratorHttpException: Error creating Transaction. Duplicate Reference. Error code: 1016 at UiPath.Core.Activities.HttpStatusCodeExtension.OrchestratorEnsureStatusCode(HttpResponseMessage response) at UiPath.Core.Activities.BaseQueueActivity.EndExecute(NativeActivityContext context, IAsyncResult result) at UiPath.Core.Activities.AsyncNativeActivity.BookmarkResumptionCallback(NativeActivityContext context, Bookmark bookmark, Object value) at System.Activities.Runtime.BookmarkCallbackWrapper.Invoke(NativeActivityContext context, Bookmark bookmark, Object value) at System.Activities.Runtime.BookmarkWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

9.png

10.png

Starting with Orchestrator 2022.10.+, the Queue item references will be stored in the permanent [UiPath].[dbo].[QueueItemUniqueReferences] table. It is recommended to upgrade the Orchestrator to 2022.10+ to have the latest improvements and bug fixes.

Example:
11.png