OLE DB join not updating when running findAndReplaceWorkspacePaths

Anonymous User · ‎10-30-2011

Original User: bwragg

Hi,

We have layers stored in File Geodatabase joined to 3 x SQL Server tables of tabular data via an OLE DB connection. We also have 1 x XY Event layer that has its datasource set as one of these SQL Server tables.

Recently the SQL Server database got migrated resulting in a hostname change for the SQL Server. As a result all the joins & relates stoppted working. The data held in the SQL Server tables is very changable and very rich in content so we have about 20 joins per mxd! Obviously I needed a quick way to fix these files. Worse yet, IT says that another migration will happen soon as this was just a temporary emergency migration.

I've been reading all the threads I can on using findAndReplaceWorkspacePaths and I've succeeded in creating a python script that runs thru a directory of mxds and using the following code to replace a workspace (not quite the code but the basic idea):

oldWorkspace=r"C:\path\to\old\connection\OldDB.odc"
newWorkspace=r"C:\path\to\new\connection\NewDB.odc"
mxd = arcpy.mapping.MapDocument(pathtomxdfile)
mxd.findAndReplaceWorkspacePaths(oldWorkspace,newWorkspace,false)
mxd.saveACopy(pathtonewmxdfile\newmxd.mxd,"9.3)

The script runs with no errors and outputs files that I can open in 9.3. When I open them in 9.3 the 3 x tables connection have been fixed, I can open then and see the data in them. Also the 1 x XY Event layer that is built from 1 of these tables is working great. So the workspace replacement code I've created has seemingly worked great.

But here is my problem....every single join and relate fails! When I look at what joins and relates are setup on the layers, every layer shows no joins or relates.

I noticed that the file takes a while to open which made me think that it may still be trying to connect to the old database. To check this I've opened the mxd file in notepad and I found that when I search thru the file there still occurs numerous references to the old database name. Also I think this occurs exactly the same number of times as the number of joins. So I believe that the code above is not replacing the join data correctly.

Any ideas what I'm doing wrong or have I misunderstood the limitations of findAndReplaceWorkspacePaths.

thanks

bwragg

Anonymous User · ‎10-31-2011

Original User: jbarrette

Have you tried

mxd.replaceWorkspaces?

Jeff

Anonymous User · ‎11-02-2011

Original User: bwragg

Have you tried

mxd.replaceWorkspaces?

Jeff

Hi Jeff,

No I hadn't tried becuase the documentation here http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00s30000004p000000 suggests that the findAndReplaceWorkspacePaths function should work.

I'll make changes to my script and see if it does anything. Have you found that this has fixed similar problems?

BTW sorry I didn't reply sooner, the notification I setup to notify me if someone posted didn't seem to work.

Thanks again for your response,

Anonymous User · ‎11-02-2011

Original User: bwragg

Hi Jeff,

I just tried the replaceWorkspace function and this is what I get:

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "c:\convert.py", line 37, in <module>
    mxd.replaceWorkspaces(oldWorkspaces,"OLEDB_WORKSPACE",newWorkspaces,"O
LEDB_WORKSPACE",False)
File "C:\Program Files\ArcGIS\Desktop10.0\arcpy\arcpy\utils.py", line 181, in
fn_
    return fn(*args, **kw)
File "C:\Program Files\ArcGIS\Desktop10.0\arcpy\arcpy\_mapping.py", line 754,
in replaceWorkspaces
    return convertArcObjectToPythonObject(self._arc_object.replaceWorkspaces(*gp
_fixargs((old_workspace_path, old_workspace_type, new_workspace_path, new_worksp
ace_type, validate), True)))
ValueError: MapDocObject: Unexpected error
>>>

any ideas? I'm running sp3

thanks

JeffBarrette · ‎11-03-2011

I've tried both mxd.findAndReplaceWorkspacePaths, and mxd.replaceWorkspaces and they are both working (using 10.0 SP3). I've set up my environment to mimic yours (I think).

TEST1:
I have a fGDB FC and a SQL Table (with an ole db connection). The MXD contains both the FC and table as well as a join from the FC to the table. I copied the same table to a different SQL Server DB and created a second connection file. I calculated some of the fields to have different values so I could determine if the join was pointing to the correct table. The functions just worked.

TEST2:
Next I tried deleting the orignal table. Thinking that you have broken sources and perhaps broken sources are the problem. In this scenario, when I reopen my MXD, my table is broken and the join on the FC no longer exists. I thought this would be the cause but the tests still run like they did above.

Jeff

Anonymous User · ‎11-03-2011

Original User: jbarrette

I just learned that even though the joins/relates don't appear for broken links that they are still persisted and capable of being repaired. My tests still run.

Is there anything else I can try? If you can't get past this I strongly recommend that you open an incident with support services.

Jeff

Anonymous User · ‎11-08-2011

Original User: bwragg

I just learned that even though the joins/relates don't appear for broken links that they are still persisted and capable of being repaired. My tests still run.

Is there anything else I can try? If you can't get past this I strongly recommend that you open an incident with support services.

Jeff

Hi Jeff,

Thanks for your help. So just to clarify, when you ran "Test 2" you deleted the original table in db1. You then opened the mxd and found the table and join were broken. You then shut the mxd and used python to adjust the workspace to the db2 connection. Then when you opened the mxd the table worked and the join was intact? Or did you need to do another step to make the join work? Sorry if I'm making you repeat yourself, just want to be sure I understand what you did.

Thanks,

JeffBarrette · ‎11-09-2011

Yes, I deleted the table from the orginal source. When I opened the MXD, the table was broken and the join/relates were missing from the table properties dialog. When I ran the mxd data source functions pointing to the new workspace, everything was reset.

Jeff

Anonymous User · ‎07-30-2012

Original User: bwragg

Hi all,

I'm resurrecting this thread becuase its reared its head again and I'm out of ideas. Since my last post after trying for weeks to get the python scripts to work, I just scrapped using python to fix any borken data sources. I assumed I had some bad mxds since other guys on this thread could run the same code ok. So I rebuilt all the mxds from scratch using ArcGIS 10.

I'm now on 10 sp5 and a SQL Server we have OLEDB connections to has changed hostnames again. So I thought I'd try to run the python scripts using replaceWorkspaces to fix the connection. The scripts run fine (no errors) and it saves out new mxds. If I run ListBrokenDataSources over the newly created mxds it says that all the data sources are fine.

When I open the mxds in ArcMap the follwing objects using the OLEDB connection are fixed:
4 xyevent layers
6 tables are fixed

Whats not fixed is:
all the joins to these tables are broken
all the relates are broken

So despite http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00s30000004p000000 saying:

"These methods work recursively through all layers and tables. This means that joined and related tables are also searched and updatedI]

I can't see how it does?

As a work around, in ArcMap I also tried opening the broken mxd and using the "Repair Data Source..." option when you right click on a layer but this doesn't fix the joins.
Also I tried using the "Set Data Sources" in ArcCatalog but this also doesn't fix the joins.

I can't think whats causing the joins to not updated. Just throwing another detail in the mix, both connections use Windows NT Integrated security.

Any ideas?

Thanks,

bwragg