Orchestrator DB maintenance SQL error for queue items when using additionalFilter

Help Orchestrator
,
1

AIM: I’m trying to delete queue items from On-Premise Orchestrator (2022.10.2) DB, but only for queues within 2 Orchestrator folders (in this example folders with OrganizationUnitId 22 or 23).

When I try to run a job in Microsoft SQL Server that will then use the cleanup scripts provided by UiPath it returns an error. “Error converting data type varchar to bigint.”

This is the Job step:

image
image1509×620 28.6 KB

This is the error

Execution finished with error(s). Please execute the following SQL to see all errors: SELECT * FROM dbo.__CleanupLog WHERE IsError = 1 AND ExecutionId = ‘FD5B0470-3A9D-48BC-8E74-AC77DE27371E’ . First error is: Error converting data type varchar to bigint. [SQLSTATE 42000] (Error 71000) Execution started, execution id : FD5B0470-3A9D-48BC-8E74-AC77DE27371E [SQLSTATE 01000] (Error 0) At the end of the execution you can view all logs of the execution using the following SQL: SELECT * FROM dbo.__CleanupLog WHERE ExecutionId = ‘FD5B0470-3A9D-48BC-8E74-AC77DE27371E’ ORDER BY Id [SQLSTATE 01000] (Error 0) Caution: Changing any part of an object name could break scripts and stored procedures. [SQLSTATE 01000] (Error 15477) Error while processing table QueueItems. Encountered the following error: Error converting data type varchar to bigint. [SQLSTATE 01000] (Error 0). The step failed.

What do I need to change to fix this error?

2

@carolmmorris

Looks like in the formula used one of the values being compared is with string and not an integer like maxminutes 180 etc…try using those as integers

Cheers

3

@carolmmorris you are getting this error

First error is: Error converting data type varchar to bigint

Why you are getting this error: The error message indicates that your SQL script is trying to convert a varchar (string) value to a bigint (integer) value, and it’s failing because the conversion is not possible. This is happening in the processing of the QueueItems table

pls write this Query in SQL and check where the data type is not same in the condition or during the joins

I know it helps you solve your issue.

5

I resolved the issue by converting Id to nvarchar.

– Get the Ids for all Chatbot Queues
DECLARE @chatbotQueues NVARCHAR(50);
SELECT @chatbotQueues = CAST(qd.Id AS nvarchar) FROM [UiPath].[dbo].[QueueDefinitions] qd WHERE qd.OrganizationUnitId IN (22, 23);

6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.