Why do GUID fields display as Text?

999
11
Jump to solution
03-02-2018 10:55 AM
Highlighted
New Contributor III

I'm currently trying to work with some tables within SQL Server that have GUID fields.  I'm running ArcGIS Desktop 10.3.1 and pulling the data through a standard database connection.  When I look at the table properties within ArcCatalog the GUID fields are listed as Data Type: GUID.  However, when I load them into ArcMap, they show as data type: String.  This is causing problems when I then try to join the query layer to another table in the database that has a matching foreign key field (that is GUID as well).  Any thoughts on this?

I also notice that when I add DB Spatial Views to ArcMap I have to identify a unique identifier field in the query layer.  However, a table does not require this.  The tables have indexed fields, so this might be the reason, but wanted to be sure.  This might be a separate topic than above though.

Reply
0 Kudos
1 Solution

Accepted Solutions
Highlighted
Occasional Contributor II

Since these are database tables, could they create a database view that joins the tables together in the database, then use the view as a query layer in ArcMap?  I wonder if this may remove some of the clunkiness...

Also, Esri GUID fields are associated with SQL Server uniqueidentifier data types.  You can see how to create uniqueidentifier columns in SQL Server...

uniqueidentifier (Transact-SQL) | Microsoft Docs 

"A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:+

  • By using the NEWID or NEWSEQUENTIALID functions.
  • By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value."

Per the second method, where it mentions converting strings...could this be a part of the underlying issue?  

How were your two tables originally created?

View solution in original post

11 Replies
Highlighted
Esri Contributor

Hi Eric,

Thanks for your post. 

This is causing problems when I then try to join the query layer to another table in the database that has a matching foreign key field (that is GUID as well).

When you add this other table into ArcMap does the GUID field remain GUID and not get switched to string?

I did a quick test:

  1. Added a GUID field to a table
  2. Added the table to ArcMap
  3. In the attribute table check the field data type, its still GUID
Highlighted
New Contributor III

Thanks Supriya.  So what I've noticed is if I add both tables to ArcMap, the join will succeed.  However, if I add only one table and try to join to a table through a database connection, I cannot select the field that I need to join with from the second table.  The list of available fields is reduced, and each of those fields are Type:Text.  Hopefully this workflow description makes sense.

Reply
0 Kudos
Highlighted
Esri Contributor

So what I've noticed is if I add both tables to ArcMap, the join will succeed. 

Do both table's fields get converted to Text and that is why the join succeeds? 

It seems weird that the field is getting converted to a text field in ArcMap when it is a GUID.

  • What is the version of the enterprise geodatabase (sde)? 
  • Are both tables registered with the geodatabase?
Reply
0 Kudos
Highlighted
New Contributor III

Do both table's fields get converted to Text and that is why the join succeeds? Yes, I believe that's what's happening.  They don't get converted until the view is added to ArcMap as a Query Layer. 

What is the version of the enterprise geodatabase (sde)? The enterprise geodatabase is SQL Server Enterprise Edition (64-bit) version 10.50.6220 SP3

Are both tables registered with the geodatabase? No.  We are using the SQL Server geometry data type.  I wish I could register them, however, our DB is structured without the use of SDE

Reply
0 Kudos
Highlighted
Esri Contributor

It sounds like this is a SQL Server database and not a geodatabase. Is that correct? In other words, there are no geodatabase repository tables in this database?

Even if it was just a database, the join should work properly:

ArcGIS functionality available for database tables that are not registered with the geodatabase—ArcG... 

Could you provide a screenshot of the tables's columns and the columns data types through SQL Server Management Studio? I'd like to know the GUID field's data type (i.e. uniqueidentifier).

Highlighted
New Contributor III

You are correct.  This is not a Esri geodatabase, it is a SQL Server database using the geometry data type.

I wish I could share some screen shots of what I'm seeing, but unfortunately content sensitivity will not allow it.  I think if you follow these steps, you'll see what I'm talking about.

  1. Add a table from SQL Server that has geometry within it (as well as a GUID field) to ArcMap
  2. Right click on the layer you just added>Joins and Relates>Join
  3. Choose the GUID field for this input layer
  4. For table to join to, navigate to another table with the same database that has a GUID that you'd like to join to
  5. Choose a GUID field to base the join on

You should see that the GUID fields are not listed as an option to choose from in the drop down.  Only Text fields show up as options.

Now try the same join except this time add the table directly to Arcmap so that it's listed in the table of contents. 

  1. Add a table from SQL Server that has geometry within it (as well as a GUID field) to ArcMap
  2. Add another table to ArcMap that has a GUID field
  3. Right click on the layer you just added>Joins and Relates>Join
  4. Choose the GUID field for this input layer
  5. For table to join to, select the table from step 2 in the drop down
  6. Choose a GUID field to base the join on

You should see the GUID field/s as an option to choose from

I'm hoping this workflow makes sense and that you can repeat it on your end.  If not let me know.

Reply
0 Kudos
Highlighted
Esri Contributor

I didn't realize the table you are working with had a spatial column. I was able to reproduce a GUID field converting to text field when the spatial column is in the table. I didn't even choose the GUID field as the Unique Identifier Field when prompted, I used a different field.

But, if the table is nonspatial the GUID stays a GUID when added to ArcMap.


Is there a reason you have to make the join by navigating/browsing to another table?

Is there a reason both items to join aren't in the MXD?

Highlighted
New Contributor III

I'm glad you were able to reproduce what I'm experiencing, and I'm also glad to hear (as you mentioned below) that the issue seems to be resolved above v10.5.1. 

Answers to your questions: I was hoping to only have to add spatial tables from SQL Server as layers within ArcMap and then join any additional tables by connecting to them within the database rather than adding them as a data source within ArcMap.  It keeps things cleaner and more light weight.

Thanks for your help in all of this.

Reply
0 Kudos
Highlighted
Esri Contributor

Forgot to mention, I was only able to reproduce the behavior mentioned in this thread in ArcMap 10.3.1. In ArcMap 10.5.1 and 10.6 the GUID format persists after the non-geodatabase table OR spatial table is added to the MXD.