layer serviceproperties does not support SQL Server direct connect

[DebbieBull]

I'm trying to help my users migrate between servers. I would like to be able to determine which server and database a layer is pointing to for its data source.  We have mxds and layer files numbering in the thousands.

I tried this piece of code from the Help:
                    if lyr.serviceProperties["ServiceType"] != "SDE":
                        print "Service Type: " + servProp.get('ServiceType', 'N/A')
                        print "    URL:         " + servProp.get('URL', 'N/A')
                        print "    Connection:  " + servProp.get('Connection', 'N/A')
                        print "    Server:      " + servProp.get('Server', 'N/A')
                        print "    Cache:       " + str(servProp.get('Cache', 'N/A'))
                        print "    User Name:   " + servProp.get('UserName', 'N/A')
                        print "    Password:    " + servProp.get('Password', 'N/A')
                        print ""
                    else:
                        print "Service Type: " + servProp.get('ServiceType', 'N/A')
                        print "    Database:       " + servProp.get('Database', 'N/A')
                        print "    Server:         " + servProp.get('Server', 'N/A')
                        print "    Service:        " + servProp.get('Instance', 'N/A')
                        print "    Version:        " + servProp.get('Version', 'N/A')
                        print "    User name:      " + servProp.get('UserName', 'N/A')
                        print "    Authentication: " + servProp.get('AuthenticationMode', 'N/A')
                        print ""

Returns:

    Database:       maint
    Server:         KCGIS-AGS10
    Service:        N/A
    Version:        sde.DEFAULT
    User name:      BULLD
    Authentication: OSA

For my ArcSDE sourced layers, it returns the name of the ArcMap host for the "Server" property, and "N/A" for the "Service" property even though the layer uses a direct connect sde:sqlserver:gisprod.  The workspacePath property of the layer is not reliable because connection files can be named <ANYTHING>.sde and we have at least a dozen ArcSDE servers in the enterprise.

[JeffBarrette]

This will be addressed at 10.1.  The same script run in 10.1 returns the attached results.


Jeff

[LaurenceClinton]

For the instance or service, I was able to use: servProp.get('Service', 'N/A'))


for layers in arcpy.mapping.ListLayers(mxd):
   
    if layers.supports("SERVICEPROPERTIES"):
        servProp = layers.serviceProperties
          
        print "Layer name: {0}, Service: {1}".format(layers.name,servProp.get('Service', 'N/A'))


Results:
Layer name: MXD2.SDE.airports_1, Service: sde:sqlserver:laurence7\sql2008r2
Layer name: MXD2.SDE.airports_2, Service: 9161

In reviewing serviceProperties, Service as opposed to Instance is being referenced.
>>> layers.serviceProperties
{u'UserName': u'sde', u'ServiceType': u'SDE', u'Service': u'9161', u'Database': u'MXD2', u'Server': u'Laurence7', u'Version': u'sde.DEFAULT', u'AuthenticationMode': u'DBMS'}

[LorindaGilbert]

I was able to use this portion of script inside of another python script that looped through the mxd's table of contents and data frames and it was able to find the database and service type so that I could change only the ones that were pointing to the server being changed.  We are also changing servers for one database and the .sde could be named anything thanks to ESRI embedding it in the mxd as a workspace path that doesn't have to relate to the current reality (have even found references to .sde's on other editor's machines that are not even working here any longer! and servers that were retired years ago! and it saved the path to our .sdes that were created on XP machines and we are now in Win 7 - therefore different user locations).

Now that I am attempting the SAME methods on individually saved layer files, IT DOESN'T work, it reports the workspace path and datasource, but when attempting to use the database and service type properties for further refinement, the script bypasses the find and replace workspace path portion of the script completely - it is NOT getting the information.  From the earlier post from jbarrette, this appears to be a bug in 10.0?  If so, this is the second bug that I have run into with python scripting for just a simple server change, and each were found through the forums but not on the known issues page of the support.  We are currently using Arc10.0 with SPs.  Arc10.1 is not an option for us at this time.