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.
Solved! Go to Solution.
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...
"A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:+
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?
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:
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.
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.
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
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:
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).
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.
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.
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.
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?
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.
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.