Definition query causes SQL error when published in map service

910
2
12-18-2018 08:51 AM
JonathanLathigee
New Contributor II

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

0 Kudos
2 Replies
JoshuaBixby
MVP Esteemed Contributor

What data type is "Subtype"?

0 Kudos
JonathanLathigee
New Contributor II

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

0 Kudos