Successful Join Results in Null Fields
Hello,
I have two datasets, 1. is a Enterprise Geodatabase Feature Class polygon layer. which is accessed via .sde connection, with read and write access.
the other 2. is a SQL table, which is accessed via a .sde connection, using a sql user with read only access.
I am joining the two datasets based on a common field. Both fields are Text, one is of length 10, the other 4000. An example of the data in each field is, 1. "BAGW01" 2. "BAGW01", they are identical.
As can be seen all items in table 2. match with something from dataset 1.
When the dataset 1 is viewed after this join, all of the fields from table 2 exist but are all NULL. What could be going wrong?
Edit: I've downloaded a local copy of the sql table to my local egdb, and joined that, that works as expected. But this isn't a solution, I need to be able to connect to the live sql data. Just thought id add this as it might give an insight into whats going on.
Edit 2: I've tried to join the table again a few times (after removing joins), now I can't open the polygon layer (dataset 1) at all, I get this error:
Assorted notes and questions:
(My experience is with Oracle, not SQL Server.)
- What version of ArcGIS Pro are you using? Each version of Pro seems to have its own set of join-related issues.
- Does anyone else at your organization have a different version of Pro you could test with? Or ArcMap?
- Just to clarify, are both tables SQL Server tables? What version of SQL Server?
- And is the second table NOT registered with a geodatabase?
- For what it’s worth, I suspect the join is one-to-one (one-to-first) because the tables aren’t in the same workspace. If they were in the same workspace, the join would be one-to-many.
A one-to-first join is also only possible if each table is from a different workspace.
- Does the second table have an ObjectId column? I suspect no, if it is not registered with a geodatabase.
- Does the join field in the first table have unique values? Can you check via an SQL query or some other way?
- Does the join field in the second table have unique values? Can you check via an SQL query or some other way?
- Related: Is ArcGIS Pro the right tool for tabular/join-based analysis?
- In the Catalog properties of each table, is Pro treating the field datatypes the same? Both as TEXT, but with different lengths? Or is one field treated as some other datatype, such as the SQL Server equivalent to Oracle’s BLOB or CLOB?
- "The name of the field does not have to be the same, but the data type must be the same; you join numbers to numbers, strings to strings, and so on." Introduction to joins and relates
- And what are the underlying datatypes in the SQL Server databases? I.e., what does a SQL client like Toad or SSRS show the datatypes as?
- In your temporary copy of the second table in the EGDB, what is the join field’s datatype in the Catalog properties?
- Can you create a database view or query layer on the second table, where you’d CAST the join field from length=4000 to length=10? And join using the database view or query layer instead of the table. So that your join fields both have the same lengths? Or at least similar lengths?
- Does the join need to allow editing on the first table? Or would a read-only workaround be suitable?
- For example, could you get your DBA to make the equivalent of an Oracle DBLINK, but in SQL Server, that would let you create a query layer on both tables using only one .sde connection?
- Does making join using Make Query Table work as expected? https://community.esri.com/t5/arcgis-pro-questions/how-to-join-using-make-query-table/m-p/1392162 That mechanism is pretty clunky, so it’d only be a very last resort.
- Are you able to submit a case to Esri Support?
- If you don’t get an answer here, you could try asking a moderator to move this question to the Data Management Community (via the Report Inappropriate Content button in the post’s options). There are some SQL Server experts over there.
- As you’ve hinted, it’s strange that the messages in the screenshot indicate that matches have been found for 137 records, yet the attribute table is not showing any matches.
- Regarding the error in Edit 2: I’ve had similar issues when a join is broken in Pro due to bugs. If it’s a throw-away APRX/Project, then I sometimes have luck using the Remove All Joins option in the attribute table. If not, then I discard my changes to the Project or create a new Project.
- Join had no matches but two attribute fields have same name
4. I think there are a few different ways you can check if a table is registered with the geodatabase or not. The way I do it is right click on the table in the Catalog pane. If the Register with Geodatabase option is shown in the right-click menu, then that means it's not registered. If that option is missing, then it is already registered.
Of course, if you know for sure that the underlying SQL Server database isn't a geodatabase, then that answers your question, too.
This table isn't registered with the geodatabase:
I think maybe the missing object_id might be causing an issue, the exported local version of the table (dataset 2) works and that has an object id field.
You could try creating a query layer in your APRX/Project on the second table. When creating a query layer, Pro will ask you what field is the unique ID field. That field will be treated as if it is an ObjectID field. You could try using the existing ID field in your table that has unique values as the unique ID field. If that doesn't work, you could try generating an integer field on the fly via SQL in the query layer. In Oracle, it'd be:
select
cast(rownum as int) as unique_id,
my_id,
...
The ROWNUM pseudocolumn is an Oracle-specific thing. So you'd need to figure out what the equivalent is in SQL Server.
Maybe Pro will behave better when joining to a query layer, compared to joining to a standalone table that isn't registered with a geodatabase (such tables are referred to as "database tables" instead of "geodatabase tables" in the Esri documentation).
