topic Databse error during reconcile reconcile: Expression type int is invalid for COLLATE clause. in ArcGIS Enterprise Questions

Databse error during reconcile reconcile: Expression type int is invalid for COLLATE clause.

DrewDowling — Wed, 20 Dec 2023 19:54:19 GMT

ArcGIS Enterprise 10.9.1

Pro 3.2

SQL Server 2016

I have branch-versioned data in a feature service. I made approx. 4000 edits in a version. Now however I cannot reconcile this version. When I do, I get a popup in ArcGIS Pro:

ERROR: A database error was encountered. Please contact your system administrator to review the server logs.

When I check the ArcGIS Server logs, I see this error:

Underlying DBMS error [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Expression type int is invalid for COLLATE clause.]

Does anybody know how to troubleshoot this? I'd like to not lose the edits.

Re: Databse error during reconcile reconcile: Expression type int is invalid for COLLATE clause.

Scott_Tansley — Wed, 20 Dec 2023 20:32:26 GMT

Branch versioning isn't something I regularly consult on, but I believe I saw a client do this. They were branch versioning but trying to traditional reconcile. Are you following the branch versioning specific instructions:

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/overview/reconcile-and-post-edits-to-a-branch-version.htm

Re: Databse error during reconcile reconcile: Expression type int is invalid for COLLATE clause.

DrewDowling — Wed, 20 Dec 2023 20:47:20 GMT

Thanks for the link Scott. The feature service and the editing workflow have been in place and working fine for over a year, so I'm sure it's not the service or how we are reconciling.

To test this I created a new version, repeated 20 of the edits and it rec and posted fine.

I've started digging into the SQL running on the database but so far can't isolate and COLLATE command using SQL Profiler.

Re: Databse error during reconcile reconcile: Expression type int is invalid for COLLATE clause.

MarceloMarques — Wed, 20 Dec 2023 21:25:04 GMT

@DrewDowling
-------------------------------------------------------------------------------------------------------------------------------

Esri Support Case 1

- Customer was facing the following error while reconciling edits on a branch versioned geodatabase

"Underlying DBMS error [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Expression type int is invalid for COLLATE clause.] [StreetNameAliasTable]" in ArcGIS Pro.

- Resolution: The issue was resolved after user re-published the service.

-------------------------------------------------------------------------------------------------------------------------------
Esri Support Case 2

- Customer gets this error in both ArcPro 2.9 and 3.1.

- Customer has a version of the database with a large number of feature service edits that cannot be reconciled/posted.

Error when reconciling branch versioned data: Underlying DBMS error [[Microsoft ODBC Driver 17 for SQL Server][SQL Server]Expression type int is invalid for COLLATE clause.]

Resolution: The customer found that reconciling more than 1000 edits at a time caused the reconcile process to fail. They developed a workaround to in which they are only reconciling 1000 (out of the 300 edits in the problematic version) edits at a time.

-------------------------------------------------------------------------------------------------------------------------------

Note: if you still encounter the issue then please open a ticket with Esri Technical Support to investigate the problem further, an Esri Support Engineer will help you troubleshoot the issue.
-------------------------------------------------------------------------------------------------------------------------------
Troubleshooting Tips

Provide Esri Support with the following information to help identify the issue.

1. Capture the ArcSDE Intercept when running ArcGIS Pro.

https://support.esri.com/en-us/knowledge-base/how-to-set-up-an-sdeintercept-log-file-on-a-client-mach-000014807

https://support.esri.com/en-us/knowledge-base/how-to-diagnose-arcsde-connection-and-performance-issue-000010355

Note: Esri Support might ask for the ArcSDE Intercept from the ArcGIS Server machine as well.

2. Capture a SQL Server Trace while running ArcGIS Pro

Create a Trace - SQL Server Profiler | Microsoft Learn

Extended Events replace the deprecated SQL Trace and SQL Server Profiler features.

https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver16

Query Store
https://cloudblogs.microsoft.com/sqlserver/2022/08/18/query-store-is-enabled-by-default-in-sql-server-2022/

-------------------------------------------------------------------------------------------------------------------------------

Re: Databse error during reconcile reconcile: Expression type int is invalid for COLLATE clause.

DrewDowling — Wed, 20 Dec 2023 23:33:51 GMT

Thanks @MarceloMarques . This is an interesting problem. I hope it gets a fix soon, as I can see our editors making large numbers of edits on particularly big projects.

For now, my fix was to do the edits in the default version. I'm the geodatabase admin so I have the permissions to edit sde.Default directly. This won't be an option if one of the editors comes across this problem.

These are the steps I used to fix the problem should anybody else come across it.

From the version that won't reconcile and contains the edits, export the table to a local FGDB
Switch to the default version, connected to Portal as a user with rights to edit sde.Default
Join local copy of data to feature layer
Use the field calculator tool to update feature layer attributes with local copy attributes.

Re: Databse error during reconcile reconcile: Expression type int is invalid for COLLATE clause.

MarceloMarques — Wed, 20 Dec 2023 23:52:17 GMT

FYI: Esri Support - Asynchronous operations are being allowed on protected default versions for portal users that are not version administrators
"Asynchronous operations are being allowed on protected default versions for portal users that are not version administrators"