Underlying DBMS error

[clt_cabq]

I have a ArcGIS Service throwing errors due to un underlying DBMS error - the text of the error message follows:

Error: Underlying DBMS error [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The geometry data type cannot be selected as DISTINCT because it is not comparable.] [AdaptLocationSummaryStatistics].

The table specified as the source is a point layer of approximately 25K records . There is also a related table (non-spatial) consisting of about 100K records. Also, this is a SQL Server database.

This service has been running for several years with no changes to the database items and has been throwing this error for about a month apparently. On a monthly basis these data are updated using a truncate/delete rows/append process. 

Any thoughts what might be happening here? 

[RachelEsri]

Hi @clt_cabq - I suspect that our published service utilizes the "returnDistinctResults = True". Depending on your version of Enterprise, you may still be hitting a potential bug. It could be relevant for your organization. 

BUG-000140112 (Querying a registered map service layer fails when the input geometry is set to 'returnGeometry = false' and 'returnDistinctValues = true') or

Return Distinct Values:  https://developers.arcgis.com/rest/services-reference/enterprise/query-feature-service-layer/

Thank you!

Rachel

[clt_cabq]

@RachelEsri appreciate your response, I'm trying to find out some answers about the version of server we are using; this particular one is a stand alone instance of AGS and not fully wrapped into our Enterprise (i'm a little unclear about the architecture to be honest). We've stopped this service for the time being but a colleague noted that when restarting it the errors only seem to spawn when a particular dashboard is opened. That dashboard uses some Arcade scripting to query that layer - is it possible that is the source triggering this problem? 

[RachelEsri · Accepted solution]

Hi @clt_cabq Appreciate the clarification. BUG-000140112 was logged against ArcGIS Server, so it would still be applicable, regardless if ArcGIS Server is standalone.

To check your version of ArcGIS Server, you can log into Server Manager (Site tab > Software Authorization). At the top of the page will show you the version. 

Viewing Software Authorization 

The dashboard utilizing scripting that queries that layer definitely sounds like it could be the source of the behavior, given the bug. Even if we are utilizing applications or scripting within our apps, it will still hit the REST endpoint of the service.

Thank you!

Rachel

[clt_cabq]

Thanks again @RachelEsri this is one of those curiouser and curiouser issues at the moment. If I view the webmap underlying the dashboard, and where the arcade scripts live in the popup section, the map seems to run fine with no errors. However, as soon as I open the dashboard and click a feature to see its popup, then that error is spawned. Do we know any reason why the dashboard app should behave differently than just the webmap? I will ask over in the dashboards forum to see if anyone has encountered this issue. 

[clt_cabq · Accepted solution]

@RachelEsri just closing the loop on this, it seems there is something corrupted the dashboard app that was causing that service to throw errors. I've rebuilt that app using the same underlying webmap and am not seeing any errors. I've tested using that REST service in multiple other contexts as well and can't replicate the errors that using the dashboard creates. Weird.