Ability to serve non-sde table in Open Data

[PhilLarkin1]

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‌

[CourtneyClaessens]

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 

[PhilLarkin1]

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.

[PhilLarkin1]

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. 

[PhilLarkin1]

 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