I'm trying to change the workspace path of a query layer using the code below and it always breaks. The layer is connected to a view in an Oracle Spatial 10g database. The database is SDE enabled although since it is a query layer SDE knows nothing of it.

The tables which python is changing the workspace path of are actually views which is why they are query layers. The problem I'm experiencing is that the layers are broken after the code is run on them. It seems the workspace path is changed correctly but the "Table Type" is (null) and it has lost its Object-ID Field mapping. Does anyone know why it corrupts the layer like this when I run the code below?

import arcpy, os

# Globals
process_folder = r"C:\Users\tfronal\mxds"
dev_workspacePath = r"C:\Users\tfronal\connections\ORACLEDEV_SDVIEWER.sde"
change_flag = False

for filename in os.listdir(process_folder):
    fullpath = os.path.join(process_folder, filename)
    if os.path.isfile(fullpath):
        basename, extension = os.path.splitext(fullpath)
        if extension.lower() == ".mxd":
            change_flag =False
            print "--------------------------------------------------"
            print "Opening MXD:" + fullpath
            mxd = arcpy.mapping.MapDocument(fullpath)
            for tbl in arcpy.mapping.ListTableViews(mxd):
                if "oraclestage" in tbl.workspacePath.lower():
                    tbl.findAndReplaceWorkspacePath(tbl.workspacePath, dev_workspacePath, False)
                    print "New workspace path: " + tbl.workspacePath
                    change_flag = True

    if change_flag == True:
        print "mxd saved."

    del mxd

print "Finished"

EDIT: Adding some additional information as per comments request. This code is meant to be used to migrate mxd/s between staging and development environments and so on. The change of workspace(.SDE file used) will change the database, username and password. The MXD is saved from ArcMap 10.2.2.

  • This question is slightly confusing, so perhaps you could provide some more detail about what you are trying to accomplish. Since you are working with a query layer, the complete table definition has 2 parts. The first, what you refer to as the workspace path, is the connection string to the database. The second part is the actual SQL query you are running on that database to create your layer. When you state that you are changing your workspace path, what are you trying to change? Do you need to change the user/password you are connecting to the database with, or something else? Dec 4, 2014 at 16:51
  • Just to be clear, this code works on the table type "SDE Table" but causes corruption when running it on the table type "Query Table". Dec 4, 2014 at 23:05
  • I see the problem now. If you are working with an SDE Table, you are working with a table that is registered through the ESRI universe of feature table, object table, etc. That functionality is an interim layer between ArcGIS and the database. When you are using a Query Layer, that the table is based off of, it is going from ArcGIS using an SQL query, directly to the database through an ODBC connection. There is no workspace, because that is an ESRI construct, thus the corruption. Dec 5, 2014 at 1:27
  • I'm afraid that I'll have to correct you on this one since I can print the workspacePath in python so it does have one. I must stress that there are spatial query layers which are NOT SDE connections and I can run python code to change their workspace correctly. Also I note that it uses an SDE file for the connection string regardless of what kind of layer it is. Dec 5, 2014 at 1:50
  • You are correct, my apologies. Let's take a step back then and look at your databases. You mentioned that you are moving from a Staging to a development environment. Are these two databases identical? More specifically, are the tables and views that this query layer is based on, identical? It makes sense that the SDE Table works fine because any ESRI registered table contains an Object ID field. In a Query Table, you have to specify the Object Id field. Is it possible that the field listed as the Object ID field, is missing in the development database? Dec 5, 2014 at 22:42