ArcGIS Pro shows SLQ fields as Object ID Type

1446
9
03-17-2021 09:48 AM
JoeBorgione
MVP Emeritus

I have an odd situation.  In ArcGIS Pro 2.7 I have a connection to a SQL Server database.  Within this database are tons of tables with tons of fields of all types.

When I make a connection to that database and open the tables in ArcGIS and then look at the design view, it randomly changes numeric fields to Object ID Type.  I stumbled upon this issue because when I performed a Table to Table operation, I would lose that field: you can only have 1 object id field in any given ArcGIS database object.

Here is what the field definition shows in Sql Server Management Studio:

JoeBorgione_0-1615998851857.png

And here are those same fields when shown in the design view in ArcGIS Pro:

JoeBorgione_1-1615998946296.png

The APDEFNKEY field does not have unique values in this particular table.  This database has many related tables with a field from each with a KEY suffix.  For example, in the APDEFN table, the APDEFNKEY value is unique to each record.  In the ADDRESS table the ADDRKEY is unique to each record.

Anyone seen this before? Is it a known 'undocumented' feature?

 

 

That should just about do it....
Tags (1)
0 Kudos
9 Replies
George_Thompson
Esri Frequent Contributor

These are tables are not part (i.e. registered) with the Geodatabase, correct?

If not, that is the first not null numeric field. My guess is that it "assumes" that the field is the ObjectId.

Are the tables being added like query layers?

--- George T.
0 Kudos
JoeBorgione
MVP Emeritus

Correct: stand alone SQL instance not even close to a Geodatabase. 

Not sure I follow you about 'not null numeric field',

I'm adding the tables as tables, not query layers.  We are seeing other things like if none of the SQL table fields are numeric (text and dates only) a field called ESRI_OID gets inserted into the Design/Fields view.  That I can accept, but the randomizing of numeric to oid is a PITA.

That should just about do it....
0 Kudos
George_Thompson
Esri Frequent Contributor

Take a look at this doc: Database data and ArcGIS—ArcGIS Pro | Documentation

I understand that there is not a geodatabase and the table is NOT spatial, but I am thinking that the software is treating it the same way when it initially reads the table. Look at #4

--- George T.
JoeBorgione
MVP Emeritus

Thanks @George_Thompson ; I could examine the tables in SQL and see if the other numeric fields have null values. Truth be told, this database is so poorly designed I'm drowning in it...

 

That should just about do it....
0 Kudos
George_Thompson
Esri Frequent Contributor

I can understand that. If the DB tables are just a jumble of fields (no logic), then you could see weird behavior. I also think that it is a good idea to have a unique idea field in the tables.

--- George T.
0 Kudos
JoeBorgione
MVP Emeritus

This thing is a circa 1995 Oracle db that got pushed into a SQL server.  There are literally hundreds of tables in it and those tables have upwards of 100 fields; most of which are <Null>.  Logic? We don't need no stinkin' logic....

That should just about do it....
0 Kudos
George_Thompson
Esri Frequent Contributor

Oh boy..........🙄

--- George T.
MatthewDriscoll
MVP Alum

I am frustrated with the same problem.  If my memory is correct in the old ArcMap it would ask you which field you want as the Unique Identifier, if you did not have one I think there was an option to have it create one.  If you don't have a field that has a unique identifier you need to create one in SQL studio first.  If you do happen to then from the Map tab select Add Data -> Query Layer.  Or drag and drop go to the Properties -> source tab -> click the pencil icon and specify the field you want as the unique identifier.   These are the only options I can find, which probably won't solve your problem as it doesn't solve mine.   Also if you go and publish the table it completely changes the attributes of the one it "chose" for you.  ESRI should not allow it to randomly change my attributes in an SQL file.  I feel your pain!

JoeBorgione
MVP Emeritus

I work strictly in ArcGIS Pro and during this process never did I get a warning to pick a unique identifier field.

That should just about do it....
0 Kudos