topic Re: Error Opening Table in Pro - "Failed to retrieve a page of rows" in ArcGIS Pro Questions

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

BenVan_Kesteren1 — Thu, 11 Oct 2018 23:16:48 GMT

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?

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

BushtelAdmin — Wed, 05 Dec 2018 01:13:43 GMT

I get the same issue, though loading a table from Oracle.

I see data when the table opens.

The table display window crashes if I scroll a few pages or try to sort.

ArcPro 2.2.4 and Oracle 11.2.0.4

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

JonathanFarmer_oldaccount — Tue, 11 Dec 2018 14:59:55 GMT

Hi Ben,

The part of your post that sticks out to me is "The table I am trying to add is a view from a Civica Authority DB...". What is the actual DBMS you are using here? What version? Also, what version of Pro?

We have had some bugs in Pro in the past with this same error but they are usually pretty specific to a certain workflow or setup.

Jonathan

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

MichaelVolz — Tue, 11 Dec 2018 15:06:36 GMT

How many rows of data does your Oracle table contain?

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

JonathanFarmer_oldaccount — Tue, 11 Dec 2018 15:26:53 GMT

Ben,

Couple more pieces of information I remembered after posting.

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

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?
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

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

BenVan_Kesteren1 — Wed, 12 Dec 2018 00:06:39 GMT

Hi Jonathan,

Just further information, Authority Database is a MSSQL 2014 12.0.5207.0

Pro version I am using is 2.2.3

Cheers

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

BushtelAdmin — Wed, 12 Dec 2018 00:38:46 GMT

The tables that fail all have > 2,000 rows.

If I just scroll down the page, the error always occurs at 200 records.

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

JonathanFarmer_oldaccount — Wed, 12 Dec 2018 15:02:07 GMT

Hi Ben,

I just tested with that exact setup and don't see the issue. I tested both with a non-spatial table and a feature class, both with over 20,000 records each.

I'm still wondering if this may have something to do with how Pro is reading the owner of the table as I mentioned in my second post yesterday. Can you actually create a new connection to this database in the Catalog pane (right click Databases > New Database Connection) and see if the same behavior occurs? What user are you using to connect to the database? You might try a different user as well.

Jonathan

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

JonathanFarmer_oldaccount — Wed, 12 Dec 2018 15:06:42 GMT

Bushtel Admin,

ArcGIS Pro will throw this error if there are records in the table that have invalid geometry or are invalid in some other way. If this always happens at the same spot, it may be worth checking to see if this is the case in your situation.

You can't run a Check Geometry on an Enterprise geodatabase but you can with a File geodatabase. It may be worth exporting this feature class out to a FGDB and running Check Geometry on it just to make sure. I don't know that this is the issue but it is where I would focus.

Jonathan

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

MichaelVolz — Wed, 12 Dec 2018 15:11:59 GMT

Jonathan:

Is there a chance that the export process to a file gdb will fix this error?

So if no errors are detected in the file gdb, then the feature class could be imported back into SDE from the file gdb (Not overwriting current feature class until problem is fouind) and it would then work as expected.

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

JonathanFarmer_oldaccount — Wed, 12 Dec 2018 15:19:13 GMT

Michael,

Yes, that is possible as well. Enterprise geodatabases automatically check the validity of each geometry when uploaded but they don't actively search out and fix invalid geometry once the data is loaded in.

Jonathan

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

BenVan_Kesteren1 — Thu, 13 Dec 2018 05:10:51 GMT

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.

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

JonathanFarmer_oldaccount — Thu, 13 Dec 2018 15:37:03 GMT

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

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

MichaelVolz — Wed, 02 Jan 2019 17:47:07 GMT

Ben:

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

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

BenVan_Kesteren1 — Sun, 06 Jan 2019 21:50:59 GMT

Hi Michael,

It is a view that lives within the Authority DB.

So no it is not a Query Layer as such.

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

Anonymous User — Tue, 13 Aug 2019 15:05:46 GMT

I am seeing this issue with Pro v 2.4 (as well as v2.3.2), when trying to open a view from an Oracle database. I cannot open the table to see the data, because it throws the exception mentioned in this thread. When I look at the table design, I am noticing that Pro is somehow assigning the 'ObjectID' data type to a field that does not contain unique values. This seems to me that Pro is interpreting this table incorrectly. I am able to open the table in ArcMap 10.6.1 without issue. There is not much that can be done on the DB side to this as this view is simply derived from a few tables that are already in production. Please keep in mind this Oracle database is the backend of our main permitting system which I have used many times for GIS automation--it's just this one particular view that I can't work with. I think it's a bug in Pro

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

MichaelVolz — Tue, 13 Aug 2019 15:15:50 GMT

Evan:

Can you provide the steps you take to add the view to Pro? I ask because I thought that you have to add a view as a Query Layer and this process asks you which field to use as the ObjectID field so you have control over the field that Pro uses for the unique id field.

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

Anonymous User — Tue, 13 Aug 2019 15:25:59 GMT

I was able to add the table successfully this way Michael, thanks. Historically I was just able to create the database connection to Oracle, find the view, add it to the map and open it in Pro. When I added it as a query layer I got the option to pick which field contains unique values, which skips Pro trying do this by itself.

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

MichaelVolz — Tue, 13 Aug 2019 15:38:22 GMT

I'm glad that worked for you with the Query Layer. Had you ever tried to add the same view to ArcMap? If so, did ArcMap ask you to identify the field that would be used as the unique id?

Also what is the field type (e.g. text, numeric - specific type, guid) that you are choosing to be the unique id field when bringing in the Oracle View as a Query Layer to Pro?

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

JoeFlannery — Wed, 16 Sep 2020 20:05:46 GMT

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!