topic Re: ArcGIS Pro shows SLQ fields as Object ID Type in ArcGIS Pro Questions

ArcGIS Pro shows SLQ fields as Object ID Type

JoeBorgione — Wed, 17 Mar 2021 16:48:02 GMT

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:

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

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?

Re: ArcGIS Pro shows SLQ fields as Object ID Type

George_Thompson — Wed, 17 Mar 2021 18:10:09 GMT

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?

Re: ArcGIS Pro shows SLQ fields as Object ID Type

JoeBorgione — Wed, 17 Mar 2021 19:01:06 GMT

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.

Re: ArcGIS Pro shows SLQ fields as Object ID Type

George_Thompson — Wed, 17 Mar 2021 19:15:44 GMT

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

Re: ArcGIS Pro shows SLQ fields as Object ID Type

JoeBorgione — Wed, 17 Mar 2021 19:23:06 GMT

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

Re: ArcGIS Pro shows SLQ fields as Object ID Type

George_Thompson — Wed, 17 Mar 2021 19:28:07 GMT

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.

Re: ArcGIS Pro shows SLQ fields as Object ID Type

JoeBorgione — Wed, 17 Mar 2021 19:34:06 GMT

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

Re: ArcGIS Pro shows SLQ fields as Object ID Type

George_Thompson — Wed, 17 Mar 2021 20:00:10 GMT

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

Re: ArcGIS Pro shows SLQ fields as Object ID Type

MatthewDriscoll — Thu, 18 Mar 2021 15:21:39 GMT

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!

Re: ArcGIS Pro shows SLQ fields as Object ID Type

JoeBorgione — Thu, 18 Mar 2021 16:02:28 GMT

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