I am encountering some issues with SQL views in ArcGIS Portal and ArcGIS Dashboard, both of which are utilizing an Azure SQL Server database. The points and data within these SQL views can be seen and symbolized in Portal and ArcGIS Pro. However, the functionality seems to be limited as I am unable to execute any of the ArcGIS Portal's analysis tools on these SQL views or generate any charts or graphs in ArcGIS Dashboard.
The logs show two main error messages:
'Geodatabase error: DBMS does NOT support this function': This error suggests an incompatibility between certain operations and our Database Management System (DBMS).
'Error: Attribute column not found [42S22:[Microsoft][ODBC Driver 17 for SQL Server][SQL Server] Invalid column name 'ESRI_OID'.]': This message indicates the system is trying to reference a column named 'ESRI_OID' that doesn't exist or isn't accessible in our database.
Through my troubleshooting, I've found that for a view to work, I need to add the data to the project using Query Layers or update the source Query after adding the data. ArcPro seems to prefer an integer column for a UID but when I create a view with an ObjectID integer column, it prevented me from publishing the view so I need to use a string ID column. Also, creating a GUID datatype column presented another issue: if the view is created with this column and then used, the data fails to load once the view is published to ArcGIS Enterprise. To fix this I have to create the GUID in the Query Layer process.
