layer workspace path points to a .sde file that doesn't exist, yet the layer still draws

[TheodoreRakel]

I've been working with a .mxd that has some broken links.  I have been using the following python to print the workspace path for each layer:

>>> for lyr in arcpy.mapping.ListLayers(mxd):

...     if lyr.supports("workspacePath"):

...         source = lyr.workspacePath

...         print "%s -> %s" % (lyr, source)

I found that layers do successfully get drawn and their data source is correct in arc map even if the workspacePath points to a .sde file that doesn't exist on the machine.  If the links aren't broken, then there's no need to fix them; but I'm curious how ArcMap is able to find the data in sde if the layer workspacePath is not correct.

[WesMiller]

Are they ".lyr" files that are drawing? I haven't looked this up yet, but I've been suspicious for a while that ".lyr" files are saved as part of the map document.

[TheodoreRakel]

No, they are not .lyr files.  They are feature classes in SDE.

[RebeccaStrauch__GISP]

I've been running in to the same thing/question.  I've been finding that the credentials to the sde are stored in the mxd and it seems to ignore the actual .sde file.  It makes it a little more difficult to update the sde connection with python, but it is possible (I'm working on it myself, and have had some success).

Just fyi - AGS services can also have unique needs when replacing connections.

Theodore Rakel   Here is a test you can try to test what I'm trying to say above.  First, make sure you have you mxd backed up.  Open the mxd, and to make it easier, copy the sde layer (in the TOC) and move it to the top....this will make the layer in the list [0].  Then go thru the commands listed below one by one ....make sure to note the place you need to "fix" the source manually.  This is one of the processes I have bee using to help figure out the issue.  I hope this helps.

import arcpy
mxd = arcpy.mapping.MapDocument("CURRENT")
dfs = arcpy.mapping.ListDataFrames(mxd)
lyrList = arcpy.mapping.ListLayers(mxd)
lyr = lyrList[0]
lyr.isBroken
lyr.dataSource
lyr.name
lyr.isBroken

# manually fix the source now

lyr.dataSource
lyr.name
lyr.name = lyr.datasetName
lyr.name

[JoshuaBixby · Accepted solution]

What you are running into is expected behavior.  Prior to ArcGIS 10.1, how an SDE connection file was accessed when first loading data determined whether the connection information was embedded in the map document or referenced in the map document:  Bug:  SDE connection information is not preserved in an MXD if a Universal Naming Convention (UNC) p...  The issue was "addressed" in ArcGIS 10.0 SP5 and ArcGIS 10.1.

Personally, I disagreed and continue to disagree that it was a bug that needed to be fixed.  The embedded vs. referenced behavior was around since the ArcGIS 8.x days, even though the aforementioned KB only covers back to 9.1.  Allowing for SDE connections to be referenced instead of embedded allowed for flexibility in managing large swathes of SDE layers, but I guess it was too complicated for most users to get their head around.  Alas, everything was changed to embedded.

Now that SDE connection properties are embedded, it is important to always use/check the embedded properties in the map document and not use a Describe object to look up the connection properties of an SDE connection file referenced with the workspacePath.  It is completely possible that an SDE connection file referenced with workspacePath could have different connection properties than when it was used to load data into a map document.

[TheodoreRakel]

Thanks.  How do you check the embedded properties in a mxd?  Can I do that with arcpy?

[MichaelVolz]

if layr.supports("WORKSPACEPATH"):

        # logfile.write("The datasetName before replacement is " + layr.datasetName + '\n')

        # logfile.write("The old connection string before replacement is " + layr.workspacePath + '\n')

        if layr.supports("DATASOURCE"):

            if layr.supports("SERVICEPROPERTIES"):

                print("The layer name in mxd in Prod is " + layr.name)

                servProp = layr.serviceProperties

                user = str(servProp.get('UserName', 'N/A'))

                serverID = str(servProp.get('Server', 'N/A'))

                serverInstance = str(servProp.get('Service', 'N/A'))

                serverVersion = str(servProp.get('Version', 'N/A'))

[JoshuaBixby]

To print out the embedded connection properties for all SDE layers in an open map document:

mxd = arcpy.mapping.MapDocument("CURRENT")
for lyr in arcpy.mapping.ListLayers(mxd):
    if lyr.supports("SERVICEPROPERTIES"):
        if lyr.serviceProperties["ServiceType"] == "SDE":
            for k, v in lyr.serviceProperties.iteritems():
                print "Property: {:<30}Value:{}".format(k,v)

[RebeccaStrauch__GISP]

Joshua, at least for some of my really old SDE connections  (still sees a version that uses port 5151), and testing using 10.2.2, sde connections do not support "SERVICEPROPERTIES"    So, that will not work.  just fyi.  May work on other broken SDE connections.

I have found a way to find them and list properties, but it's a process of elimination....not quite as straight forward as the above.  fwiw.

[MichaelVolz]

Rebecca:

Do you really need to restore such old port 5151 connections?  In order to make your life easier (less coding) you might think about just resourcing direct connections and let endusers resource the older port 5151 connections themselves with the Set Data Source tool available in ArcCatalog.