Problem with SDE/SQL join

[DonFreeman]

Box1 contains an SDE geodatabase using SQLServer Express 64 bit. Box2 contains a SQLServer2008 (full version) database. Features from the SDE geodatabase are added in ArcMap. An attribute table from Box2 is added to the project. A join is attempted with the features and the attribute table. The join field is of type double (number with 2 decimals). There are no funky field names in either table. When clicking the validate join button, the message says there were no matches (not true). After clicking the OK  button, the join completes and the joined table is appended to the field set and data is visible when the attribute table is opened. However when using identify, the joined attributes all come up as null. Can someone help me fix this? Ultimately, the project will be published to ArcGIS Server with the intent of delivering live data. So, making a copy of the attribute table is not an option.

Thanks

[JakeSkinner]

Try upgrading to the latest service pack (SP4) for ArcGIS Desktop and ArcSDE.

http://resources.arcgis.com/content/patches-and-service-packs?fa=viewPatch&PID=66&MetaID=1843

http://resources.arcgis.com/content/patches-and-service-packs?fa=viewPatch&PID=66&MetaID=1841

I ran a quick test and I could not reproduce this with ArcGIS Desktop 10 SP4, and ArcSDE 10 SP4 for SQL Server.

[VinceAngelo]

Joins on real values are notorious for lack of reliability.  You might want to consider
creating an integer factor (*100) or string equivalent, to take the floating point
representation issues out of the equation.

You can also gain significant performance benefit by using the SQL engine to drive the
joins (in the database, vice in ArcGIS), mostly through the use of views. 

Joins across databases are not supported; this even more true for joins across servers,
but you can probably make this work if you initially create a table in the the local database,
then after the app is working, rename the table and configure a foreign table reference
in its stead.  Performance in this situation is often poor (vice local tables, due to the lack
of indexes, caching, or optimizer configuration), and most folks usually wind up using some
form of replication to push the foreign table into the local database as the long-term solution.

- V

[DonFreeman]

Thanks for the suggestions. I am upgraded but that has not solved the problem. I thought I would try converting the join fields to integer as suggested. I created the integer field in the SQL but cannot in the SDE because the table is locked. I can't figure out why. Is there a way to determine who/what has it locked?

Also, perhaps closer to the root of the problem, I can see the SQL data when the table is added to the project (open table). But in Catalog, if I attempt to export the data to a new free standing dbf, it produces the schema but no data (table is empty) Perhaps a review of the connection is in order? I started with [Database Connections]/[Add OLE DB Connection], then selected [SQL Server Native Client 10.0]. Then filled out the server, user, and password fields, checked Allow Saving Password and provided the database name. This all seemed to work fine except for the trouble I am having. Is there a specific user setup permissions required in the SQL database?

[DonFreeman]

Problem seems to be solved by dropping 2 fields (varchar(MAX)) from the sql table. Apparently esri doesn't like blob fields. So does any one have suggestions for a workaround?

[VinceAngelo]

According to the documentation, ArcSDE supports varchar(MAX) as an SE_CLOB_TYPE column,
but if you're using ArcGIS to access through OLE DB, there might be other limitations on the
supported types.

- V

[DonFreeman]

According to the documentation, ArcSDE supports varchar(MAX) as an SE_CLOB_TYPE column,
but if you're using ArcGIS to access through OLE DB, there might be other limitations on the
supported types.

- V

Vince - You are probably right. My guess is that it's an OLE DB problem. All I know is that when I changed the varchar(MAX) fields to varchar(8000), it works as expected. Maybe ESRI will fix their drivers in a future release? This problem has cost me 2 full days of work. You would think if the validation checker can tell you there is a problem that they would carry it to the next step and tell you what the problem is. 😞


Thanks

[VinceAngelo]

Esri can't fix it if they don't know it's broke.  Please contact Tech Support to start an incident.
This is also the best way to get a supported work-around.

- V