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?

[Anonymous User]

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

[MichaelVolz]

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.

[Anonymous User]

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.

[MichaelVolz]

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?

[AlisonCorey]

Hi-

I'm having the same problem. I have SQl Server views. Only the first 200 records are visible.

I'm curious have you found a solution for this issues?

Thank you in advance (fingers crossed),

Alison

[Bud]

As the replies in this post suggest, you need to make sure the values in your unique ID column are truly unique. Otherwise, you'll get the error.

[NeilFordyce]

I had the same problem in PRO 2.5.2. Creating a query layer worked despite it looking at the same data via the query. My OID is autogenerated unique numeric value.

If you go to the properties of the data layer it appears that ArcGIS Pro by default is choosing the first non-nullable text field as the unique identifier. As my OID is autogenerated it ignores this until I go to properties and edit query layer and chose "OID".

OID is created in SQL using ROW_NUMBER() OVER( order by blah blah blah) +1000000

What a terrible assumption for the programmers to make. OID is my first field in the table and is unique. Why not this?

I make sure that the coordinate fields used to create the spatial view are not null and that there are no spaces in the field names. Geometry is created using GEOMETRY::Point

Seems that query layer with this version is the only way to go.

[DougBrowning]

I think this got me.  I have a database view doing a Union all on two feature classes.  Of course there are dup objectids so I chose a different field.  BUT Arc wants a 1 to 1 with the Shape field and the objectid.  Well if one is a table no biggie but if both have a Shape field and dup objectids it gets confused.

You can see it happen

"When you create a spatial view, you must include the ObjectID column from the same table as the spatial column. If the ObjectID from the feature class is not included, the unique relationship between the ObjectID and shape column may be violated, resulting in inaccurate results when queried or rendered in ArcGIS."

from https://desktop.arcgis.com/en/arcmap/10.3/manage-data/gdbs-in-oracle/views-in-geodatabase.htm 

Kinda makes sense but it is for sure a bummer.  I need to show two different feature classes as one.  It took months before we noticed too.

Does this sound correct?  I cannot find anything on it.  How can I show 2 layers as 1?

thanks

[DougBrowning]

Forgot to come back to this.  I ended up making my own object id field with a different name.  Then one of the datasets I calc in the object id and the other I calc object id+ 1,000,000.  That is way over my record count so no dups. Then tell the view to use this new object id field that is now unique.  Works perfectly now.

[Bud]

What me and a colleague are doing, which seems to work well (Oracle):

Use ROWNUM to generate a unique column.

select
    cast(rownum as number(38,0)) as rwonum_,   --or OBJECTID, or whatever
...

or

select
    cast(rownum as integer) as rownum_,
...