Definition query causes SQL error when published in map service

[JonathanLathigee]

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:

SEVERE

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].

Testing/201812LakesOne.MapServer

And layer 2 returns:

SEVERE

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].

Testing/201812LakesOne.MapServer

Update the definition queries as follows:

Layer 1:

[SHAPE].STArea() > 400000

(ie square brackets)

Layer 2:

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

Thank you

[JoshuaBixby]

What data type is "Subtype"?

[JonathanLathigee]

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