Join to SDE from SQL table dropping records

[MattReeves]

We have setup a Join on a unique field between two tables. One table is an SDE polygon feature, the other is a SQL table. We have set up the join to data through an .odc connection to the SQL location. After running the Join, we are noticing that not all records are joining up. After manually comparing the records that have not joined, we cannot find any reason why the join isn't working on these select features. We have manually verified that the records are exactly the same. It is a very perplexing issue.
Has anyone seen this issue of dropped joins when Joining to a SQL table from an SDE feature on a common field?

On a side note, I exported these records out of SQL to a .dbf table, and I am, without any trouble, able to join up with the SDE feature on all records.

[JoshuaBixby]

Is your SDE polygon feature versioned?  If so, you should be joining with the versioned view and not the base tables.

[VinceAngelo]

What is the datatype of the join column?

Using ODBC to join spatial and non-spatial data in databases is the long way to implement a view.

- V

[KirstenFrost_Andersen]

Hi Vince, I've been looking at this issue with Matt, too.  Do you have a better suggestion to join the two tables?  He's using a non-spatial database connection to join to an SDE connection (there's no ODBC set up on the computer).  I tried in 10.4 to connect two *.sde connections to this same data and the join doesn't result in any match at all.  The spatial database is non-versioned, the field is nvarchar(255).  We don't have the authority to make any changes here.  The non-spatial is varchar(15).  The values that aren't matching up have been verified in both places as being 10 characters exactly.

[VinceAngelo]

The problem statement is still far from clear.

Please provide the exact version of the database software.

Are you using a string conversion function to make sure the UTF-8 and UTF-16 strings are

really compatible?

Why not create a view?

- V

[KirstenFrost_Andersen]

I guess I don't know how a view would help here.  We have a spatial database feature class with a field for parcel numbers, nvarchar(255).  We have a non-spatial table with a varchar(15) field for parcel numbers.  Both are on different SQL2008R2 servers.  In ArcMap 10.3.1/10.4 we have the feature class as a layer. We join that layer to the table on the respective parcel number fields using ArcMap's Join Data UI (found by right-clicking on the layer).  There's no conversion going on. 

[VinceAngelo]

The fact that they were in different servers wasn't explicitly set forth until just now. 

Conversion is going on, just not in a way over which you have control.  It might be instructive to generate a hexadecimal dump of the UTF-8 representation from each table, to see if any escape codes have hidden in the strings.

- V