eSearch 3.6.4 + Server 10.2 oddity

5941
14
Jump to solution
03-24-2014 02:29 PM
BrianOevermann
Occasional Contributor III
I recently upgraded our Server from 10.1 to 10.2.  I also took the opportunity to roll to the latest version of the Flex Viewer (3.6) from 3.4.  Prior to upgrading the Server, I prepped the new viewer and everything was working great.

Once I upgraded the Server to 10.2, however, I now get an error when I attempt to do a search against my parcel layer.  The error is "[RPC Fault faultString="Unable to complete operation." faultCode="400" faultDetail="An SQL statement with comments and/or semicolon is invalid."].

The parcel layer has joined Assessor data, but nothing changed with the map service (other than upgrading to 10.2) nor did I change anything in the eSearch config for this layer.

The relevant code block from the eSearch config is:
  <layer>    <token/>    <definitionexpression></definitionexpression>    <enableexport>true</enableexport>    <name>Parcels</name>    <url>http://server-name:6080/arcgis/rest/services/General_Mapservices/parcels/MapServer/2</url>    <expressions>     <expression alias="Parcel ID" textsearchlabel="Search by Parcel ID:">      <values>       <value prompt="Example: 2724069035">coivector.COI.Parcels.ParcelPIN = upper('[value]')</value>      </values>     </expression>      <expression alias="Owner Name" textsearchlabel="Search by owner name:">      <values>       <value prompt="">Assessor_Info.GISMAP.%ParcelInfo.PropertyOwner LIKE upper('%[value]%')</value>      </values>     </expression>     <expression alias="Plat Name" textsearchlabel="Search by Plat Name:">      <values>       <value prompt="Example: ENGLEWOOD ADD">coivector.COI.Parcels.PlatName LIKE upper('%[value]%')</value>      </values>     </expression>     <expression alias="Site Name" textsearchlabel="Search by site name:">      <values>       <value prompt="Example: GILMAN VILLAGE">Assessor_Info.GISMAP.%ParcelInfo.PropertyName LIKE upper('%[value]%')</value>      </values>     </expression>     <expression alias="Appraised Land Value" textsearchlabel="Search using appraised land value:">      <values>       <value prompt="include operator i.e. > 500000">Assessor_Info.GISMAP.%ParcelInfo.AppLandVal [value]</value>      </values>     </expression>     <expression alias="Appraised Improvements Value" textsearchlabel="Search using appraised improvements value:">      <values>       <value prompt="include operator i.e. > 1000000">Assessor_Info.GISMAP.%ParcelInfo.AppImprVal [value]</value>      </values>     </expression>     <expression alias="Total Appraised Value" textsearchlabel="Search using total appraised value:">      <values>       <value prompt="include operator i.e. > 2000000">Assessor_Info.GISMAP.%ParcelInfo.TotalAppVal [value]</value>      </values>     </expression>     <expression alias="Appraised Land Value-sq. ft." textsearchlabel="Search using appraised land value per sq. ft.:">      <values>       <value prompt="include operator i.e. > 90">Assessor_Info.GISMAP.%ParcelInfo.AppLandValSF [value]</value>      </values>     </expression>     <expression alias="Appraised Improvements Value-sq. ft." textsearchlabel="Search using appraised improvements value per sq. ft.:">      <values>       <value prompt="include operator i.e. > 200">Assessor_Info.GISMAP.%ParcelInfo.AppImprValSF [value]</value>      </values>     </expression>     <expression alias="Total Appraised Value-sq. ft." textsearchlabel="Search using total appraised value per sq. ft.:">      <values>       <value prompt="include operator i.e. > 200">Assessor_Info.GISMAP.%ParcelInfo.TotalAppValSF [value]</value>      </values>     </expression>     <expression alias="Commercial Building Gross Total Sq. Ft." textsearchlabel="Search using commercial building total gross sq. ft.:">      <values>       <value prompt="include operator i.e. > 500000">Assessor_Info.GISMAP.%CommBldg_GrossSF_by_PIN.CommBldgTotalGrossSF [value]</value>      </values>     </expression>    </expressions>    <graphicalsearchlabel>Use one of the graphical search tools to select parcels</graphicalsearchlabel>    <spatialsearchlayer>true</spatialsearchlayer>    <titlefield>ParcelPIN</titlefield>    <fields all="false">     <field name="coivector.COI.Parcels.ParcelPIN" alias="Parcel PIN" gridfield="true"/>     <field name="coivector.COI.Parcels.PlatName" alias="Plat Name" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.PropertyName" alias="Property Name" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.PropertyOwner" alias="Property Owner" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.OwnerAttention" alias="Owner Attention" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.OwnerAddress" alias="Owner Address" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.OwnerCityState" alias="Owner City-State" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.OwnerZipCode" alias="Owner ZipCode" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.Plat" alias="Plat" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.Lot" alias="Lot" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.Block" alias="Block" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.YearBuilt" alias="Year Built" gridfield="true" gridfieldonly="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.LotSF" alias="Lot Sq Ft" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.AppLandVal" alias="Appraised Land Value" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.AppImprVal" alias="Appraised Improvements Value" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.TotalAppVal" alias="Total Appraised Value" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.TotalAppValSF" alias="Total Appraised Value Sq Ft" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.AppLandValSF" alias="Appraised Land Value Sq Ft" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%ParcelInfo.AppImprValSF" alias="Appraised Improvements Value Sq Ft" gridfield="true"/>     <field name="Assessor_Info.GISMAP.%CommBldg_GrossSF_by_PIN.CommBldgTotalGrossSF" alias="Commercial Building Total Gross Sq Ft" gridfield="true"/>    </fields>    <icon isfield="false"></icon>    <links/>    <zoomscale usegeometry="true" zoompercent="2"></zoomscale>    <autoopendatagrid>false</autoopendatagrid>   </layer>


These queries have worked through multiple versions of the eSearch widget (with appropriate config file tag adjustments), so I am at a loss as to why there is suddenly an issue.  Could the error be referring to something in the result set rather than the SQL query syntax itself?

FWIW, the same map service is successfully used in the Identify widget.  A similar table of joined info is used with my site address layer and successfully performs search queries as expected.  It uses the same database connection as the joined info in the parcel service.

I'm not sure if this is an eSearch issue or something with Server 10.2.  Nothing in the "What's new in 10.2" seemed to indicate that esri did anything major with with SQL queries and map services, but that doesn't mean a minor change somewhere has created this issue.

Any thoughts?
Thanks.
Tags (2)
0 Kudos
1 Solution

Accepted Solutions
RobertScheitlin__GISP
MVP Emeritus
Brian,

   I have seen this a time or two, that when a server upgrade is done a change can occur to field names with no indication given to the end user except that a query no longer woks. So my suggestion is that you (forget for a moment that it use to work) and carefully examine the REST Endpoint field names and compare what they use to be and what they are currently. This examination need to include the titlefield element as well (currently yours stands out to be because it does not use the fully qualified name).

View solution in original post

0 Kudos
14 Replies
RobertScheitlin__GISP
MVP Emeritus
Brian,

   I have seen this a time or two, that when a server upgrade is done a change can occur to field names with no indication given to the end user except that a query no longer woks. So my suggestion is that you (forget for a moment that it use to work) and carefully examine the REST Endpoint field names and compare what they use to be and what they are currently. This examination need to include the titlefield element as well (currently yours stands out to be because it does not use the fully qualified name).
0 Kudos
BrianOevermann
Occasional Contributor III
Robert,

Good catch on the title field entry not being fully qualified.  Unfortunately that did not solve the problem.

In my attempt to resolve this issue, I deleted the parcel map service and rebuilt it--not just re-published but created a brand new .mxd to publish.  I also started from scratch with the esearch.xml file.  One query against the Parcel PIN (the first expression in the code block from my original post) was created and the entirety of the output fields shown.  (completely typed with no copy/paste just to ensure that there were no "special keystrokes" inadvertently entered).  Result was the same error.

Removed all of the fields that are from the joined table and the search query works as expected.  When I added one of the fields from the joined table, the result was the error.

On a whim--and I kick myself for not doing this earlier--I went into the REST service browser and attempted to perform a query against the property owner field of the jointed table.  Result is the same error thrown from within the eSearch widget.

So... this is NOT an issue with your eSearch widget but rather a bug or something with the way map services with joined tables are published in 10.2.

In a previous version of Server there was some joined table "wackiness", so I guess that has been re-introduced.  My workaround before was to export the layer with the joined table to embed the join fields, then use that to publish my map service.  That works, but it adds an additional process to all of my maintenance whenever either the parcels or the Assessor data changes (which is at least monthly for the Assessor).

What is strange is that I have my site addresses joined with a table from the same database, so the "structure and source database" of the join is the same.  THAT one works, though I've now probably just caused it to fail by the mere mention of it!

Thanks, Robert, for the fresh look at my config on this.
0 Kudos
BrianOevermann
Occasional Contributor III
Robert, I am marking your response as the "solution" so you get the points.  Even though it did not solve this particular issue, it is sound advice for anyone with a similar issue.
0 Kudos
RobertScheitlin__GISP
MVP Emeritus
Brian,

   So are you actually on 10.2 and not 10.21? I know they addressed a lot of server issues in 10.21, but I can not recall if there was something specific to joined map services or not.
0 Kudos
BrianOevermann
Occasional Contributor III
Robert,

Correct.  I am on 10.2.  The only item in the "Server/REST issues fixed at 10.2.1" list that might seem to apply is:

NIM095513
MapService "query" operations should be linked to "data" capability, not "query" capability
of the MapService.

But that might be a stretch.  Contemplating if I should try to arrange another upgrade (to 10.2.1) this weekend if IT staff are available or first put in a support request.  My hunch is that there isn't a solution, unless 10.2.1 fixes it, until a patch, service pack, or new release is put out.
0 Kudos
RobertScheitlin__GISP
MVP Emeritus
Brian,

   I would call tech support first as they can search the issue database and let you know if this is a known issue and if it was resolved in 10.21.
0 Kudos
BrianOevermann
Occasional Contributor III
I thought I would give an update on my issue...

I opened a support incident with ESRI and after a number of back-and-forth discussions/screen shares plus research on ESRI's end, we believe we found the source of the problem and a workaround.

To summarize, when I add my SQL Server-based tables to ArcMap for joining to my feature class(es), ArcMap adds a '%' symbol in front of the table name.  [see the message chain above for complete context].

This is NOT an issue with the Flex API (nor Robert's eSearch widget), the issue occurs at the REST endpoint.

It turns out that dragging and dropping a table from the catalog window adds the '%' to the table name.  If I use the "Add data" tool from the toolbar or the File menu, the '%' does not get added.

So, the ESRI support tech is going to do more research to determine if there is a bug or if this behavior is by design, but at least I can change my "data adding habits" for SQL Server tables to use the 'Add data' tool and not drag and drop.

I will try to update this thread again once the ESRI support tech gets back to me regarding the "bug or design" determination.
RobertScheitlin__GISP
MVP Emeritus
Brian,

   Glad you are making some headway on this. I would have never found that drag and drop issue as I almost never use that feature.
0 Kudos
BrianOevermann
Occasional Contributor III
...And I rarely use the "Add data" tool now that there is a Catalog tool docked on one side of my ArcMap interface.  It's sad that it never occurred to me that ArcMap might treat different ways of adding data, well, differently!  I was trying every other possible variation I could think of.

What baffles me is that there was a similar issue at Server 10.0 that got "fixed" at 10.1 but "broke" again at 10.2.  But you won't see me volunteering to compare millions of lines of Server code to attempt to find the "fix". 🙂
0 Kudos