Queries wont run in Portal on SQL Table

Question

Hi All, appreciate any assistance on the following, I am connecting to a SQL Server database containing a table with X&Y's.  I add the table to ArcGIS as an X,Y event layer with the objective of publishing content to Portal for ArcGIS.  The data does publish in a Web Mapping Application however when I attempt to run the queries I immediately get "query failed !" message.  I had also intended to create a Dashboard view of data but when I try to point at the data source in Dashboard for ArcGIS I get the warning triangle that seems to suggest it can't connect to the data source.  I have a suspicion that the problem is there is no OID column in the database but I don't have the permissions to go and create that column as I don't own the database.  Any suggestions as to how this can be resolved ?

ChrisPedrezuela · Answer

I suggest publish a query layer instead of an xy event layer. There should be a sql function to create a shape geometry  based on your x and y column.

How To: Insert geometry from XY coordinates using SQL

If the data is not too big I think you can make it work, but the more data you have on the table, the heavier the load it takes to run the query against the database to render on the map. If it lags in rendering in a webmap, best to save the data in a featureclass and just write a refresh script process from sql to fc daily to get updates daily.

RamlaShihab1 · Answer

check the rest end point of the server , can you query it.