I have a publicly shared feature service published to Portal that includes a join through a read-only account to an SQL view in one database and a read-only account to our Roads layer in another database.
- Works when viewing the service in a browser.
- Works when viewing the map in Explorer while logged into Portal
- Does not work when viewing the map through Explorer when logged into AGOL (they will need to be logged into AGOL and cannot be logged into Portal)
Meanwhile, while we try to access the layer through Explorer, we are getting the this error in the server log:
An invalid where clause or definition expression has been requested: "GIS_Data.A1.Roads.GlobalID,GIS_Data.A1.Roads.OBJECTID,GIS_Data.A1.Roads.ROAD_NAME,PW.PW_RO.%LatestPavementLiftVW.roadStatus,GIS_Data.A1.Roads.SHAPE
- The SQL view is a query on a feature class tied to a public survey.
- Join performed and service published through ArcGIS Pro.
- Couldn't publish from ArcMap. Error 001270 couldn't consolidate data and also (non-critical) 24040, layer has an in-memory join.
- Tried to join the service created of the view (https://webgis.durhamnc.gov/server/rest/services/PublicWorksServices/Engineering_Inspections/MapServer/5) with an existing service for our roads (http://durhamnc.maps.arcgis.com/home/item.html?id=afb81b5c61fb40e68a2ad38d71162247) but could never view the records for the view, and got a message that the layer hadn't drawn completely.
- I tried to publish just the view and roads in the same service but without the join, with the intention of performing the join online, but got error 00062 - that the map contains layers from two different databases. This was confusing as the sources were always two different databases and it was publishable before.
- I then tried to publish the view on its own, but have not been able to view records for it (https://webgis.durhamnc.gov/portal/home/item.html?id=bf55d646c98f4409b03a33dd2d6bbe64). I get the error that it cannot be drawn.
- I took out some fields, including the objectID, GlobalID, and shape fields and published it again, but still no dice. I can't open the attribute table.
- I add the GlobalID and objectID back in but can no longer overwrite the service.
- Created new MAP service (not feature service) with everything but the SHAPE field and a few text fields, but still no dice. I could publish it, but no attribute table pops up. I did this because I realized we wouldn't need a feature service and was hoping that just doing a map service would work, but it didn't.
Thank you in advance for any insights on this! I have not posted to the forum before but did search for an answer and was unable to find anything specifically related to SQL views and viewing from Portal to AGOL. Apologies if I missed something.