Error Opening Table in Pro - "Failed to retrieve a page of rows"

[BenVan_Kesteren1]

Hey, 

I have been able to add a table using sql server in ArcMap Desktop for some time, I am now trying to read the table within an ArcGIS Pro v2.2.3 Map, but I keep getting the error Failed to retrieve a page of rows and I am unsure why this is happening. The layer adds to the map fine (seems to display all 20,000+ polygons), its just when I try to view the Attribute Table that this error appears.

Note it is able to load the column headers in the background, so I am assuming it is a data issue.

The table I am trying to add is a view from a Civica Authority DB (MSSQL 2014 12.0.5207.0), so its not spatially enabled at all.

Has anyone been able to overcome this issue before? Or even know a way for me to troubleshoot the cause?

[BenVan_Kesteren1 · Accepted solution]

Hey, 

So today I have spoken with ESRI Australia Support team, and they have gotten to the bottom of the issue.

I had not noticed that my View U_PROPERTIES_ASSESSMENTS did not have a unique field being used for its primary key. There were several rows that were duplicated, once these rows were filtered out of the view, it works fine in both ArcMap and ArcPro.

So what appears to be the difference between ArcMap and Pro is that if this view is added to Desktop, this software finds the first instance of each unique primary key, and keeps this row, after this it ignores any duplicate primary, and the user is none-the-wiser about the duplicated being ignored.

But what is happening with ArcPro is that pro will draw all the shapes on the map, all 20,000+, but it refuses to load the table due to a column being used as a primary key which contains duplicate values. This is not handled very well in comparison to ArcMap so I think it should be marked as a bug in this instance.

That being said, I also think ArcMap are not being warned that the view being used does not contain the full list of features.

The bottoms line (TLDR), I needed to make my primary key column unique for ArcPro to accept my data.

Cheers for all your comments.

[JonathanFarmer_oldaccount]

Thanks for letting me know Ben! Glad they were able to sort to the bottom of it. I believe the way you've described how things work in Pro is the way they designed it to work. That being said, a better error message could be helpful.

You can always ask Esri Austrailia to speak with our Support team here and log an enhancement to Pro if you like on this issue.

Jonathan

[MatthewDriscoll]

Same thing happens with SQL.  We even assigned the primary key, added an objectid field, added unique numbers, and every time it gets put into a Pro Map it switches the field primary key/objectid to the first integer column it finds.  I agree this should be a bug as it is still happening years later.    

[BrandonKrejckant_at_Psomas]

@BenVan_Kesteren1 Afternoon, how did you find out which column was being used as primary key? I assumed it would be the Object ID field. 

[DougBrowning]

Index in properties may tell you?  If I remember right this happened to me when the key was not actually unique across all records.

[MichaelVolz]

Ben:

Is this data being brought into Pro as a Query Layer?

[BenVan_Kesteren1]

Hi Michael, 

It is a view that lives within the Authority DB. 

So no it is not a Query Layer as such.

[Bud]

In a SQL query, you could use GROUP BY and COUNT to find duplicate values. 

[JonathanFarmer_oldaccount]

Ben,

Couple more pieces of information I remembered after posting. 

I've run into this exact issue most often with these 2 problems:

1. The column headers have spaces, invalid characters, unsupported characters. See more below. But looking at your video, I think you are OK here.
   1. FAQ: What characters should not be used in ArcGIS for field names and table names? 
2. The field data types are not supported in ArcGIS. Since you said this was a non-spatial database table, this is also something to look at.
   1. DBMS data types supported in ArcGIS—Help | ArcGIS Desktop 

One thing that does stand out from that video. Do you see in ArcMap where the table is in the DBO schema? So it says "authlive.dbo.tablename". When you go to add the table in Pro, I also see the same schema. But when you actually add the table to the TOC, it comes in as "authlive."Albury/Benva".tablename". 

I just tested with Pro 2.2.4 on my end and all tables in the DBO schema come in under the DBO schema just like I expect. So is this happening with all tables in the DBO schema in Pro for you? Or just this one? Might be worth taking a closer look as to why this is happening.

Jonathan

[JoeFlannery]

Jonathan:
Item #1 solved my problem.  The alias name for one of my fields had commas.  I removed the commas and this issue no longer persists.

Thank you!