AnsweredAssumed Answered

Definition query causes SQL error when published in map service

Question asked by jlathigee@crd.bc.ca on Dec 18, 2018
Latest reply on Dec 18, 2018 by jlathigee@crd.bc.ca

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

Outcomes