Pro SQL Table OBJECTID

212
6
02-01-2021 08:25 AM
Labels (1)
MatthewDriscoll
MVP Regular Contributor

When I pull in a SQL table into a Pro map it switches the OBJECTID field.  Is this a bug?  Is there a work around?  Using 2.6.2

 

objectid.jpg

Tags (3)
0 Kudos
6 Replies
jcarlson
MVP Regular Contributor

Can you elaborate as to what kind of SQL Database you're using? And in that database, what are the properties of the field OBJECTID? Is it separate from an enterprise geodatabase, or is it a table in your SDE?

From your ArcMap screenshot, it appears that the field allows null values, which is a no-no for what should be a unique identifier field. It's possible that Pro is seeing that and defaulting to the next best unique integer field? Or does the table have multiple primary keys, perhaps, and Pro is stopping at the first key field listed? Perhaps one of the geodatabase devs can speak to that.

The behavior in your screenshot suggests that the OBJECTID field is really just a field in the table, and not a "true" ojbectid field that is added and managed by ArcGIS.

If it's possible for your situation (I don't know what the source DB looks like), I would completely remove the existing OBJECTID field and register the table with the geodatabase. That would add a "true" objectid field to the table, which ArcMap and Pro would pick up consistently and correctly.

If that kind of solution is not an option, you can always add the table as a Query Layer, which gives you the option of overriding the unique identifier column(s) on the table prior to adding it.

- Josh Carlson
Kendall County GIS
MatthewDriscoll
MVP Regular Contributor

This is SQL table downloaded from the state every night.  It isn't a ESRI table.   After is gets downloaded we figured, per ESRI documentation, to add the field "OBJECTID".  This is all done with the  SQL Server Agent.  It does not allow nulls.  It works as should in old ArcCatalog.  If you right click it in Pro Catalog -> Event -> Fields it works as it should.  If you bring it into ArcMap it asks which one I want to use, but defaults to have OBJECTID being already picked.  Only when you bring it into a Map in Pro it switches to the first field.  

0 Kudos
jcarlson
MVP Regular Contributor

Well, so much for the easy solution. The difference between the Catalog and Map → Fields behavior in Pro is curious.

Does the table have more than one primary key?

Can you create a copy of the table and change the field order so that OBJECTID is higher and see if that changes anything?

- Josh Carlson
Kendall County GIS
MatthewDriscoll
MVP Regular Contributor

We did try assigning the OBJECTID field we created as the primary key in the agent as well, same behavior.  It shows correctly in everything and changes when we put it in a map.  I will call support and see if they come up with anything.  Thanks for the help!  @KoryKramer 

jcarlson
MVP Regular Contributor

No problem, sorry we couldn't figure it out. If support is able to get this solved, please come back here and post it and then mark your own reply as the solution. I think this would be something worth publicly documenting for other folks.

- Josh Carlson
Kendall County GIS
0 Kudos
MatthewDriscoll
MVP Regular Contributor

Just found this post.  It describes the same problem for the most part.

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

 

0 Kudos