No attribute table entries when loading database view to ArcGIS Pro

[TorstenLange]

Hello, I successfully loaded a database view from an SQL Server DB into ArcGIS Pro (v2.3.2). Only problem: I cannot see any data in the attribute table. Also export to shape crashes ArcGIS Pro or is incomplete. I know, the data cannot be modified, but I need to review some of the content. Any suggestions?

The error warning in the screenshot says: No page with row data available.

[George_Thompson]

What is the version of SQL Server being used?

Have you tried a newer version of ArcGIS Pro?

[TorstenLange]

The SQL Server version is 14.0.1000.169. Unfortunately, our IT department doesn't update the ArcGIS Pro version quite often.

[George_Thompson]

Is the DB view from tables within the same DB?

Are there other views, in the same DB, that work as expected?

[TorstenLange]

Yes, all queried tables are within the same database and the view is the only one. The point geometry column is added on the fly from X, Y coordinates.

As you suggested, I created a simple view from only one table, which works perfectly.

I guess, the problem is connected to the oid, that ArcGIS tries to create. By pulling the view from the catalog to the map, the simple view is opened with a dialog requesting the primary key columns, but the complex view is just loaded without any dialog. If I try to apply a definition query in the properties dialog of the dataset the ESRI_oid shows just values of 1 to 3 and the query cannot be applied (error message).

To force assigning primary key columns I tried to use 'add data' -> 'query layer' which allows me to select the relevant key columns, but it doesn't change the behavior at all.

The complex view covers 9 tables including subqueries and serveral unions. Could there be limitations regarding query complexity of views?

[George_Thompson]

Not sure of limitation on the complexity. It seems that you need to look at the complex view ObjectID field. I have seen where the field is not configured correctly and can cause issues. Views in an enterprise geodatabase—ArcGIS Pro | Documentation 

Does the complex query work correctly within SSMS?

The other option is to simplify the query (if possible) and check along the way to making it more complex.

Geodatabase‌

[George_Thompson]

Here is another post talking about the ObjectID; https://community.esri.com/thread/261330-get-sql-views-to-completely-draw-in-agol 

[TorstenLange]

Thank you for your answers! It took a bit, but as far as I can see, I got it right now. First issue was 4 unexpected double matches in the data view. Fixing, however, it didn't solve the problem. Then I created an extra table to hold unique OBJECTIDs for all objects from the queried tables, which also didn't solve the problem in the beginning.

After a while I got the impression it may be related to the data type of the OBJECTID, which was bigint. Changing the type to int finally made it successful.

Is type bigint an issue with ArcGIS?

Also, trying to register the view in the geodatabase wasn't possible. Although, I read that registering is only required for doing certain things with the data in ArcGIS Pro and it could made reading a bit faster.

However, the errors from the python command

arcpy.RegisterWithGeodatabase_management("DATABASE.dbo.vw_boreholes", "OBJECTID","geom","POINT")

were:

ERROR 001050: Registrierung in der Geodatabase ist bereits erfolgt, oder das Dataset kann nicht geöffnet werden.

                             -> Data set is already registered in the geodatabase or it can't be opend
ERROR 000308: Ungültiger Feldtyp

                             -> Invalid field typ

ERROR 001050:

I couldn't find any hint where in the database I could check if the view was already registered. How can it be done? Another issue could be related rigths on the database side. The connection is OS based, so, theoretically, everything should be fine?

ERROR 000308:

Not sure, what that means - could it be my geometry column? That's how it is created:

       geometry:: STGeomFromText('POINT(' +
                                                        CAST(CAST(bores.XCOORD AS decimal(14, 6)) AS varchar) +
                                                        ' ' +
                                                        CAST(CAST(bores.YCOORD AS decimal(14, 6)) AS varchar) +
                                                        ')', 25832
                                                       ) as geom

[George_Thompson]

Here are the supported field data types - ArcGIS field data types—ArcGIS Pro | Documentation   This may take care of most the issues.

Here is the information with registering a view - Register a table or view with the geodatabase—ArcGIS Pro | Documentation 

If it says it is already registered, maybe look in the gdb_items table for something with that exact name.

[TorstenLange]

Ok, thank you, George! I think for the time beeing I have what I need. Regarding the registering I followed the documentation. I'll figure it out later.