Error when accesing SQL Server view: "Failed to retrieve a page of rows"

[A_Schwab]

I've connected to a SQL database and I'm trying to load a (non spatial) table in Arc Pro v3.

The table shows up in my catalogue and I can add it to my layer list under "standalone tables" but when I try to open/view the table, the column headers load but then I get an error before any data is shown:

"Failed to load data. Error: failed to retrieve a page of rows"

I've tried adding directly from the catalogue and through the Make Query Layer toolbox, with the same result for both.

Based on suggestions in other threads, I've also added a UID column to the table view and removed spaces from column names, but still get the same result.

The table has 72,000 rows and 5 columns. It loads fine in PowerQuery and SQL Server Management Studio, so I know the credentials work. The error message doesn't contain enough information for me to pinpoint why the process fails.

Any advice would be much appreciated.

[Br1an_Peters]

any replies for this? 

https://pro.arcgis.com/en/pro-app/latest/help/data/tables/table-view-errors.htm

[DanNarsavage_IDWR]

I suspect you're up the same creek as this person (and now me too) -- the column(s) you've selected for your OID are non-unique.

https://community.esri.com/t5/arcgis-pro-questions/error-opening-table-in-pro-quot-failed-to-retriev...

To confirm, I queried my view with these two queries in SSMS . . .

SELECT count(*) FROM [myBigUglyView]

SELECT count(distinct(concat([myFirstIdentifier], [mySecondIdentifier]))) FROM [myBigUglyView]

If those two queries produce different numbers, you're up my creek.

[A_Schwab]

In my case the OID column was unique. I spoke to ESRI support but they wouldn't help as our version of SQL Server was outside the supported versions.

In the end I used a python script to connect to the database using pyodbc, which worked perfectly.