topic Re: Ability to serve non-sde table in Open Data in ArcGIS Hub Questions

Ability to serve non-sde table in Open Data

PhilLarkin1 — Fri, 12 May 2017 00:01:42 GMT

Solution: Don't use ESRI_OID.

TLDR: Downloading CSV's fails on Open Data. Data is served from Map Services hosting Query Layers from non-sde databases.

I have tables in MSSQL databases that I would like to offer on our Open Data site. I have published map services with these tables added as Query Layers, connected as either NT user ArcGISSOC and a database account. The service publishes fine and I'm able to see rows in the Data and Table tabs of AGOL and Open Data Admin site. REST queries to these services returns data as expected. Unfortunately when attempting to download the data as a spreadsheet I receive a generic error '...Something went wrong with the server....'. Logs on the server give a string of errors: 'General geodatabase error occurred , Error performing query operation. Wait time of the request to the service ... has expired, Unable to process request, no instances for .... were available for 60 seconds.'

Environment:

ArcGIS Server 10.3

SQL Server 2008 R2

Example REST Endpoint

AGOL layer page

Open data page

I'd like to know if Open Data is configured to accept Map Services with Query Layers configured in this way. If this isn't accepted, I'd like to know if there is an alternative that does not involve uploading CSV's to AGOL.

Thanks for your help

Courtney Claessens‌ , Klara Schmitt‌

Re: Ability to serve non-sde table in Open Data

CourtneyClaessens — Fri, 12 May 2017 15:25:54 GMT

Hi Phil,

This table should work. I recommend filing a ticket with support so they can get to the bottom of the errors you're seeing.

Additionally, I noticed that you're on a very old version of the Open Data app. Do you have plans to upgrade soon? The new versions are more stable and offer more functionality.

Thanks -

Courtney

Re: Ability to serve non-sde table in Open Data

PhilLarkin1 — Fri, 12 May 2017 16:23:34 GMT

Hi Courtney, thanks for your reply. I'll see what support has to say.

Our Open Data site is at v1.9. We are transitioning to 2.0.

This issue occurs at the new version as well.

Re: Ability to serve non-sde table in Open Data

PhilLarkin1 — Fri, 12 May 2017 16:49:17 GMT

This might be related to a timeout setting. Smaller tables work fine. (string formatting in CSV is a bit mangled, but that is a different issue)

Temporary Example: Dataset | Spokane County GIS Data Catalog

I tried bumping up the timeout duration on the service but that didn't help.

Re: Ability to serve non-sde table in Open Data

PhilLarkin1 — Fri, 04 Aug 2017 19:46:11 GMT

After much discussion with support the problem is related to using ESRI_OID.

We use ESRI_OID in query layers to overcome the problem of not having a naively assigned unique column. It appears that queries generated from Open Data don't adequately account for this field. This is odd because querying ESRI_OID from the REST endpoint works. Too bad it isn't recognized through the Open Data spec.
Recommendation from ESRI rep was to add a unique column to all tables shared out to Open Data.

On a related note, for those struggling to get accurate row counts in Query Layers, assigning a new field with a unique value is possible:

SELECT ROW_NUMBER() OVER (ORDER BY parcel) AS row_num

FROM table