I created an mxd with 2 layers. One layer has the definition query:
SHAPE.STArea() > 400000
The second layer has the definition query:
Subtype IN (1,2,3) and Shape.STArea() > 2000
Both of these definition queries were built in the query builder, verified with no errors, and then I inspected the attribute tables for the 2 layers and confirmed that the queries were correctly filtering the data.
I shared the mxd as a service, and analyzed it before publishing with no errors.
On querying the service, layer one returns:
Dec 17, 2018, 3:44:15 PM
Geodatabase error: Underlying DBMS error [[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near ')'.] [GISPRD.DBO.hyd_Poly].
And layer 2 returns:
Dec 17, 2018, 3:51:46 PM
Geodatabase error: Underlying DBMS error [[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '1'.] [GISPRD.DBO.hyd_Poly].
Update the definition queries as follows:
[SHAPE].STArea() > 400000
(ie square brackets)
Subtype IN ( '1' , '2' , '3' ) AND [SHAPE].STArea() >2000
(ie escape ‘in’ values with apostrophes and square brackets).
Now both layers can be queried correctly
Can you please explain what’s going on here? And where can I find some documentation to avoid these kinds of pitfalls when publishing services?
ArcMap 10.2.2.3552; ArcServer 10.41
In SQL, it's an int field. It's a domained attribute, so in the query builder, when I choose 'get unique values' it comes back with (for example) '1 - Pond'; '2 - Lake', and when I double click on a single value, it puts a '1' (without apostrophes into the SQL query. But like I say, in the actual SQL table, it's an int