Map Service Joins on Non-Spatial SQL Table

[EmilyLaMunyon]

Hello,

In an attempt to get data from a SQL data source, I have created a join between one of my map service layers and the SQL table. It seemed to work and the SQL layers are visible in my rest service. However, when I try to use the joined service in Flex Viewer 2.1, especially in my Search widget, I am getting errors. Does anyone know if this will work?

Thanks!

[JonFisher]

Hello,

In an attempt to get data from a SQL data source, I have created a join between one of my map service layers and the SQL table. It seemed to work and the SQL layers are visible in my rest service. However, when I try to use the joined service in Flex Viewer 2.1, especially in my Search widget, I am getting errors. Does anyone know if this will work?

Thanks!

It will work, but you need to make sure that you specify the full field name listed on your service's REST page, which will likely include the table name. For an example, see:
http://maps.tnc.org/ecadpubprod-anon/rest/services/conservation_projects_anon_WM/MapServer/1

Which works with the search widget using the following xml:
  <layer>
   <name>Conservation Projects</name>
   <url>http://maps.tnc.org/ecadpubprod-anon/rest/services/conservation_projects_anon/MapServer/1</url>
   <expression>lower(CP_STEWARD.PROJECTS.PROJECT_NAME) LIKE lower('%[value]%')</expression>
   <textsearchlabel>Find TNC Conservation Projects by Name:</textsearchlabel>
   <graphicalsearchlabel>Find details about conservation projects by clicking on one of the tools below, then clicking on the site you want to find out more about (read the help for details).</graphicalsearchlabel>
   <fields all="false">
    <field name="CP_STEWARD.PROJECTS.PROJECT_NAME"/>
    <field name="CP_STEWARD.PROJECTS.PROJECTORG"/>
    <field name="CP_STEWARD.PROJECTS.CONTACTORG"/>
    <field name="CP_STEWARD.PROJECTS.CONPRO_URL"/>
   </fields>
   <titlefield>CP_STEWARD.PROJECTS.PROJECT_NAME</titlefield>
   <linkfield>CP_STEWARD.PROJECTS.CONPRO_URL</linkfield>
  </layer>

[BrianKratcha]

I was able to get the join to work fine they way Jon explained above for version 2.1, but not 2.2 for my Query Widget. The only way my widget works is if I set <fields all="true">.  I soon as I turn it to false the Widget will not work. I set it to false I get this error in my widget when it opens.

[RPC Fault faultString="Error#2032:Stream Error.  URL: <mine URL>/query?

I have no problem using the field filter on un-joined layers.

Any ideas anyone?

Thanks,

Brian

[JonFisher]

I was able to get the join to work fine they way Jon explained above for version 2.1, but not 2.2 for my Query Widget. The only way my widget works is if I set <fields all="true">.  I soon as I turn it to false the Widget will not work. I set it to false I get this error in my widget when it opens.

[RPC Fault faultString="Error#2032:Stream Error.  URL: <mine URL>/query?

I have confirmed that the Query Widget also works for me at 2.2. Brian, I would start by verifying that the code below works in your viewer (if not, there's something wrong with your build), then if that works try the query widget with a single field (making sure spelling, case, any periods, etc. are copied exactly correctly from REST), and if that works it's likely a typo. The only other thing I can think of is making sure that the account you're using in the mxd has view access to both the feature class and the table. Good luck!

this code works in both 2.1 and 2.2:
<?xml version="1.0" ?>
<configuration>
    <layer>http://maps.tnc.org/ecadpubprod-anon/rest/services/conservation_projects_anon/MapServer/1</layer>
<!-- To return ALL records rather than a subset, use 1=1 as the query -->
    <query>CP_STEWARD.PROJECTS.PROJECT_NAME LIKE '%River%'</query>
    <titlefield>CP_STEWARD.PROJECTS.PROJECT_NAME</titlefield>
    <linkfield>CP_STEWARD.PROJECTS.CONPRO_URL</linkfield>
<!-- Rather than specifying each field, you can simply return all fields by changing "false" to "true" below and deleting or commenting out the field name tags. Note that if you don't manually specify an alias the alias from your mxd will be used (or the field name if no alias exists) -->
<fields all="false">
<field name="CP_STEWARD.PROJECTS.PROJECT_NAME"/>
<field name="CP_STEWARD.PROJECTS.PROJECTORG"/>
<field name="CP_STEWARD.PROJECTS.CONTACTORG"/>
<field name="CP_STEWARD.PROJECTS.CONPRO_URL"/>
</fields>
<!-- The filterfield lets a user filter or search within the results above for a single field -->
    <filterfield>
        <name>CP_STEWARD.PROJECTS.CONTACTORG</name>
        <alias>Filter by Organization</alias>
</filterfield>
    <refreshrate></refreshrate>
    <zoomscale>1155581.108577</zoomscale>
    <info>widgets/InfoTemplates/InfoPopupWidget.swf</info>
</configuration>

[BrianKratcha]

Hi Jon,

I tried your code and I was able to get that to work, but I am still having problems with mine.

2.1 Code (Works)

<?xml version="1.0" ?>
<configuration>
     <info>widgets/InfoTemplates/InfoPopupWidget.swf</info>
    
     <layer>http://pse-dt-7-1010:8399/arcgis/rest/services/Scenic_Rivers_Electric_Flex_Viewer/MapServer/2</layer>
    <query>SREC.DBO.tblBlinks.MomentaryInterruptions > 3</query>
    <fields all="false">
        <field name="SREC.DBO.ServicePoint.SERVICE_MAP_LOCATION" alias="Service Map Location"/>
        <field name="SREC.DBO.ServicePoint.Phase" alias="Phase"/>
        <field name="SREC.DBO.tblBlinks.MomentaryInterruptions" alias="Momentary Interruptions"/>
    </fields>

    <refreshrate>100</refreshrate>
    <zoomscale>50000</zoomscale> 
   
  
</configuration>

2.2 code (does not work)

<?xml version="1.0" ?>
<configuration>
     <layer>http://pse-dt-7-1010:8399/arcgis/rest/services/Scenic_Rivers_Electric_Flex_Viewer/MapServer/2</layer>
     <fields all="false">
     <field name="SREC.DBO.ServicePoint.SERVICE_MAP_LOCATION"/>
    </fields>
    <refreshrate></refreshrate>
    <zoomscale>50000</zoomscale>
    <query>SREC.DBO.tblBlinks.MomentaryInterruptions > 3</query>
    <info>widgets/InfoTemplates/InfoPopupWidget.swf</info>
</configuration>

2.2 code (Works, but returns all fields)


<?xml version="1.0" ?>
<configuration>
     <layer>http://pse-dt-7-1010:8399/arcgis/rest/services/Scenic_Rivers_Electric_Flex_Viewer/MapServer/2</layer>
     <fields all="true">
     <field name="SREC.DBO.ServicePoint.SERVICE_MAP_LOCATION"/>
    </fields>
    <refreshrate></refreshrate>
    <zoomscale>50000</zoomscale>
    <query>SREC.DBO.tblBlinks.MomentaryInterruptions > 3</query>
    <info>widgets/InfoTemplates/InfoPopupWidget.swf</info>
</configuration>


The error says something about null values.  Could that be my problem?  I don't understand what changed from 2.1 to 2.2 to cause this error.

[RobertScheitlin__GISP]

Brian,

   There are significant changes to the search widget from 2.1 to 2.2. In 2.2 they added the ability to return coded values domain results and more. The issue probably lies in those updates. Maybe the api team will chime in.

[EmilyLaMunyon]

Hello,
I am still not able to get a join on a layer and sql table to work in the Search widget, even in Flex Viewer 2.2. Is there something I am missing?

Thanks!

<layer>
            <name>Surveys</name>
            <url>http://gis.slco.org/wwwrest/services/public/Surveyor/MapServer/1</url>
            <expression>surveyor.SLCOSU.Surveys.DOCUMENT_N LIKE '%[value]%'</expression>
            <textsearchlabel>Search by Survey Number  [ Example: S2010010018 ]:</textsearchlabel>
            <titlefield>surveyor.SLCOSU.Surveys.DOCUMENT_N</titlefield>
            <linkfield></linkfield>
            <fields all="true">
                <field name="surveyor.SLCOSU.Surveys.DOCUMENT_N"/>
            </fields>
        </layer>

[RobertScheitlin__GISP]

Emily,

   The fact that I can not even get a simple query to work from the REST services directory using your url tells me that you still have an issue with the join. You might try adding the ArcGISSOC and ArcGISWebServices users as users to the Database that your join is pointing to if your table is in oracle or SQL Server. Best bet would be to call esri tech support.