Update: This only occurs when the layer being queried has a join. It doesn't matter if it has a join to a different database or not. And the query that fails is not trying to access columns in the joined table.

In order to more accurately express the problem, I've used fully-qualified (4-part) column names below where required. See the updated WHERE clause below.

Original question:

We are using ArcGIS Server 9.3.1, Java, and ArcSDE with SQL Server 2005 sp2.

It would be very useful for us to be able to monitor our data periodically for defects, with a query such as

select * from sdeDLM.SDE.Lang_Polygons

In ArcMap, I can do that query using Select by Attributes. I enter the above where clause and click 'Verify', and the expression is verified successfully.

But in order to monitor the data we need to use the REST API. We can successfully do REST queries with a where clause such as


or just


Those work just fine. FWIW, the URL for that REST query is:


But when I try a REST query with a where clause like

    WHERE sdeDLM.SDE.Lang_Polygons.ISO_LANGUAGE_CODE_ID <> LEFT(sdeDLM.SDE.Lang_Polygons.ID , 3)

the REST API returns the message:

Unable to perform query. Please check your parameters

As best I can tell, it's the LEFT() function that makes the difference. I have been able to successfully use where clauses with everything else in them, including '<>'.

The REST API docs for querying a layer say that "Any legal SQL where clause operating on the fields in the layer is allowed."

The URL for the query that gives an error is:


Is there a way to get the REST API to accept a where clause using Left()? Or a way to find out why it's not working?

I tried browsing the latest logs in C:\Program Files\ArcGIS\server\user\log. The file Server-20100929-143218.dat shows the following output from the time when the query failed:

String request received. Request size is 2629 characters.
QueryFeatureData2 has started.
Preparation for method: QueryFeatureData2 has started.
Preparation for method: QueryFeatureData2 has completed.
Query Operation for method: QueryFeatureData2 on layer &apos;Language Polygons&apos; has started.
**GeoDatabase Error :An invalid SQL statement was used.**
QueryFeatureData2 has completed.
Method failed.HRESULT = 0x80040207 : This is a FACILITY_ITF error that is specific to the interface that returned this error. See the documentation of the interface that returned this error for information about this HRESULT.
ERROR INFO = An invalid SQL statement was used.

But why is the SQL statement invalid? Clearly the WHERE clause has been tested as valid elsewhere. And why would some of my WHERE clauses be accepted as valid, but not the one with LEFT() in it?

  • 1
    What is the data type of the 'ID' field?
    – mwalker
    Oct 1, 2010 at 22:32
  • Try the following, instead of the current LEFT method you're using, to see if it helps: LEFT(CAST(ID AS VARCHAR) , 3) Oct 2, 2010 at 15:19
  • @mwalker: in ArcMap the data type in Text (length: 10). In SQL Server it's nvarchar(10).
    – LarsH
    Oct 4, 2010 at 14:53
  • @Michael Todd: Thanks for the suggestion. I tried that; no change in behavior.
    – LarsH
    Oct 4, 2010 at 15:00
  • 1
    You can create a multi-versioned view if you need to access the data outside the context of ESRI products. The only problem I have with that is that it seems to add several seconds to a query that should take microseconds (so it's a bit slow, but it still works). Again, this would just be a workaround until someone can figure out why you're having this issue. Oct 6, 2010 at 2:27

2 Answers 2


I know in ArcGIS Desktop, ArcSDE field names must be enclosed in double quotes. Maybe this SQL reference will help.

  • Thanks for this reference. Strangely, when I click on the Help for "Select by Attributes" in ArcMap, it says that with ArcSDE dbs there should be no quotes or brackets. But the ref you gave said there should be double quotes. However, I tried the REST query with double quotes, and it still didn't work.
    – LarsH
    Oct 4, 2010 at 14:59

As a temporary workaround (since you're accessing the data via non-ESRI products), you can use a multi-versioned view to access the data.

A multi-versioned view will help because it takes into account the deletes and adds that represent each version; that way you don't have to worry about missing data when reading directly from the layer table via SQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.