Record counts - publishing from ArcGIS Pro vs ArcGIS Desktop (ArcMap)

[JayHodny]

Hello.  We are working with spatial views created in MS SQL Server Management Studio.  We have linked servers allowing us to retrieve data (e.g., code violations) from other SQL databases and then join these data with parcel information retrieved from our ArcGIS sde SQL database.  We then publish the view as AGOL hosted feature service (for public access as we do not have ArcGIS Enterprise/Portal yet).  We noticed the published feature service does not honor the record count of the spatial view feature class when the service is published from ArcGIS Pro.  If we pull the view into Desktop and publish to AGOL, the record count is honored.  Desktop prompts us for a unique ID field during the “drag and drop” into the map frame from Catalog – Pro does not prompt for a unique ID.  We are creating a dynamic unique ID attribute in the spatial view query on the server-side to ensure all records are returned. 

We don’t understand why publishing from Pro would drop records - which is undesirable.  We do not know how to remedy such an occurrence, so we publish from Desktop as a work around.  We theorize that multiple records having identical information in each field (e.g., more than one of the same code violations in the same parcel, etc.) are being treated as one record when publishing to AGOL via Pro, ignoring the dynamically calculated unique ID values.  We want to show all the code violations, even if they are the same, by parcel.  Any thoughts?

[KevinDurkee]

Did you end up getting a solution for this? I'm having a similar but less complicated issue with publishing a view in desktop (arcmap) vrs publishing it in ArcPro.

[Bud]

I don't have experience with publishing layers or AGOL. But with query layers and database views, 9 times out of 10, my problem is that the values in the unique ID field aren't actually unique (unbeknownst to me) or the unique ID field has nulls. ArcGIS seems to automatically exclude rows in those cases.

I talked about it recently here: https://community.esri.com/t5/arcgis-pro-questions/edit-query-layer-sql-select-statement-question/m-...

Also, query layers seem to automatically exclude rows where the SHAPE is null or the geometry is empty: Query Layers — Include features with null shape in attribute table

Maybe you could check for scenarios like those and omit problem rows from your query via SQL or editing the data.

Registering a database view with the geodatabase often solves issues with record counts, selections, etc.
Register a table or view with the geodatabase

My environment is Oracle 18c, not SQL Server. Does SQL Server have some sort of ROWNUM column that you could use as a unique ID field? If it were Oracle, I'd do it like this, using Oracle's ROWNUM pseudocolumn functionality:

SELECT
    CAST(ROWNUM AS INT) AS UNIQUE_ID,