topic Re: Arcgis server is truncating the where clause when sending query to SQL server. in Developers Questions

Arcgis server is truncating the where clause when sending query to SQL server.

vikashkumar1 — Thu, 19 Dec 2019 09:24:08 GMT

When i make a query request to a map layer with some where clause to filter the response, the arcgis server truncates the where condition while executing the query on SQL server.

I am dealing with very large data set of approx 600 millions. The arcgis version is 10.4.1

The map layer is binded with a SQL view.

Re: Arcgis server is truncating the where clause when sending query to SQL server.

JoshuaBixby — Thu, 19 Dec 2019 19:30:32 GMT

Is the truncation consistent in terms of length? if so, how many characters are we talking about and what type of structure for the WHERE clause.

Re: Arcgis server is truncating the where clause when sending query to SQL server.

Lavanyaalla — Fri, 20 Dec 2019 05:57:07 GMT

The truncation is not in terms of length.Where clause length is less than 50 characters.

Example: ReadRate30Days <= 100 AND ReadRate30Days >= 0

Re: Arcgis server is truncating the where clause when sending query to SQL server.

vikashkumar1 — Fri, 20 Dec 2019 06:42:52 GMT

The truncation is not in terms of length. WHERE clause length is less than 50 characters.

Example: ReadRate30Days <= 100 AND ReadRate30Days >= 0

Re: Arcgis server is truncating the where clause when sending query to SQL server.

vikashkumar1 — Fri, 20 Dec 2019 09:32:46 GMT

I am querying map layer with following condition:

where: ReadRate30Days <= 100 AND ReadRate30Days >= 0 orderByFields: ReadRate30Days DESC

Ideally in SQL Sever the query should be genarted like below by arcgis server

select * from OAWarehouse.Map_PM_Electric_Service_Point_View) a where ( ( ( ReadRate3Days <= 100 AND ReadRate3Days >= 0 ) ) AND ( ReportingDate >= '2019-10-09 00:00:00' and ReportingDate <= '2019-10-09 00:00:00' )) AND (Shape.STIntersects(geography::STGeomFromWKB(@P1,@P2)) = 1) ORDER BY ReadRate3Days DESC

but it is generated like this in sql server

select * from (select * from OAWarehouse.Map_PM_Electric_Service_Point_View) a

I am not able to understand why this behaviour ?

Re: Arcgis server is truncating the where clause when sending query to SQL server.

MichaelVolz — Fri, 20 Dec 2019 11:47:27 GMT

Is the GIS layer presented as a query layer using the view? This behavior occurs with my data that is a view from a non-SDE spatially enabled Oracle database where the where clause gets dropped when sorting subqueries. I believe in my case it has something to do with the uniqueID field that is used as the identifier when creating the query layer is not set up with the correct format. It has been difficult to pin down and has only allowed this data to be used in limited operations instead of across the enterprise.

Did you determine the where clause was being dropped with software such as WireShark?

Re: Arcgis server is truncating the where clause when sending query to SQL server.

JoshuaBixby — Sat, 21 Dec 2019 22:24:30 GMT

This is one you will likely have to work through with Esri Support. Looking at your screenshot of the REST API call, everything seems fine in terms of how you have structured the call. If the REST call is valid, the next question is how exactly the Electric Service Point layer is created. It seems like it is a view filtering a larger data set, so then what tables are involved and what is the view's SQL?