topic data source SQL Server Express databases in Python Questions

data source SQL Server Express databases

TonyAlmeida — Sun, 12 Dec 2021 06:26:17 GMT

I need to update about 25 mxd's layers data source. The layers old data source is an SDE workspace, but now have moved to a SQL Server Express databases. In the help They indicate that i have to a create a spatial database connection to the SQL Server Express database. I am not sure how to create a spatial database connection and update the mxd's layers in python.
I would appreciate any help.

Here are some of the MXD's layers that i need the data source updated on.

Layer IMPACT AREA old location (C:\Users\t**a\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog\DBO.DSD.VECTOR.sde\dsd."CC\TALMEIDA".DEVELOPEMENT\dsd."CC\TALMEIDA".CURRENT_IMPACT_AREA)

New IMPACT AREA location(C:\Users\t**a\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog\DSD15_SQLEXPRESS.gds\DSD\DSD.DBO.DEVELOPEMENT\DSD.DBO.CURRENT_IMPACT_AREA

Layer FLOODWAY old location (C:\Users\t**a\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog\DBO.DSD.VECTOR.sde\dsd."CC\TALMEIDA".FEMA09\dsd."CC\TALMEIDA".FEMA09_FLOODWAY)

New FLOODWAY location (C:\Users\t**a\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog\DSD15_SQLEXPRESS.gds\DSD\DSD.DBO.FEMA09\DSD.DBO.FEMA09_FLOODWAY)

Layer Zoning old location (C:\Users\t**a\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog\DBO.DSD.VECTOR.sde\dsd."CC\TALMEIDA".MUNICIPALITY\dsd."CC\TALMEIDA".CURRENT_ZONING)

New Zoning location (C:\Users\t**a\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog\DSD15_SQLEXPRESS.gds\DSD\DSD.DBO.MUNICIPALITY\DSD.DBO.CURRENT_ZONING)

This is my current code.

import arcpy, os
from arcpy import env
from arcpy import mapping


arcpy.env.overwriteOutput = True

path = r'C:\GIS\MAPBOOK\Proposed Zoning Book\test'
for fileName in os.listdir(path):
   fullPath = os.path.join(path, fileName)
   if os.path.isfile(fullPath):
      basename, extension = os.path.splitext(fullPath)
      if extension == ".mxd":
         mxd = arcpy.mapping.MapDocument(fullPath)
         print fullPath
         print mxd
         for lyr in arcpy.mapping.ListLayers(mxd):
            if lyr.name == "PROPOSED ZONING":
                lyr.replaceDataSource(r"C:\Users\t**a\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog\DSD15_SQLEXPRESS.gds\DSD\DSD.DBO.MUNICIPALITY", "SDE_WORKSPACE", "DSD.DBO.FUTURE_LAND_USE_ZONING")
            elif lyr.name == "CITY LIMITS":
                lyr.replaceDataSource(r"C:\Users\t**a\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog\DSD15_SQLEXPRESS.gds\TonyOneWay\TonyOneWay.DBO.Canyon_Features", "SDE_WORKSPACE", "TonyOneWay.DBO.City_Limits")            
         print "Successfully updated data sources"
         mxd.save

It gets stuck on line 21 with this error

C:\GIS\MAPBOOK\Proposed Zoning Book\test\Proposed_ZoningMapBook_Page_10.mxd
<geoprocessing Map object object at 0x02C0F740>

Traceback (most recent call last):
  File "C:\GIS\Python Scripts\Change Data Source MXD 2.py", line 21, in <module>
    lyr.replaceDataSource(r"C:\Users\talmeida\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog\DSD15_SQLEXPRESS.gds\TonyOneWay\TonyOneWay.DBO.Canyon_Features", "SDE_WORKSPACE", "TonyOneWay.DBO.City_Limits")
  File "C:\Program Files (x86)\ArcGIS\Desktop10.1\arcpy\arcpy\utils.py", line 181, in fn_
    return fn(*args, **kw)
  File "C:\Program Files (x86)\ArcGIS\Desktop10.1\arcpy\arcpy\_mapping.py", line 680, in replaceDataSource
    return convertArcObjectToPythonObject(self._arc_object.replaceDataSource(*gp_fixargs((workspace_path, workspace_type, dataset_name, validate), True)))
ValueError: Layer: Unexpected error

Re: data source SQL Server Express databases

JamesCrandall — Thu, 30 Jan 2014 18:31:07 GMT

You just navigate to "Database Connections" in the ArcCatalog tree and go to Add Database Connection.

Do you have an SDE instance installed on your SQL Server?
http://resources.arcgis.com/en/help/main/10.1/index.html#/Enabling_SQL_Server_Express_to_store_geodatabases/018t0000000w000000/

Re: data source SQL Server Express databases

TonyAlmeida — Thu, 30 Jan 2014 19:22:57 GMT

my data is going from SDE SQLServer to Desktop(my compputer) SQL Server Express database.
For some reason it's not like my current code.

I really would appreciate any help.

Thanks.

Re: data source SQL Server Express databases

JoshuaChisholm — Thu, 30 Jan 2014 19:55:47 GMT

For what it's worth, I got a weird "Layer: Unexpected error" when I was trying to do this kind of thing a ways back. It ended up being a bug (bug NIM062937 I think) where you can't have the same feature class set name as an embedded feature class name. For example, that would cause problems if you had a feature class called 'roads' in a feature set also called 'roads'. I think ESRI fixed it but it might be worth looking into.

This posting also may be a good place to start your quest. Good luck!

Re: data source SQL Server Express databases

TonyAlmeida — Thu, 30 Jan 2014 21:12:46 GMT

I figured out my problem, it was becasuse i was forgetting to the Validate "" at the end of lyr.replaceDataSource.
After adding it my code ran with out problems, but when i opened up my mxd to check to see if the layers data source was redirected i noticed that it wasn't. So i decided to put to make a copy of the mxd after the script runs.
I opened up the new created copy and noticed that the layers that i am trying to redirect have a red ! next them.

So i right click on one and select properties and i noticed this the source tab
Data Type: SDE Feature Class
Feature Class: dsd."CC\TALMEIDA".CURRENT_ZONING
Database: C:\GIS\MAPBOOK\Proposed Zoning Book\test\Database Servers\DSD15_SQLEXPRESS.gds
Feature Dataset: DSD."CC\TALMEIDA".MUNICIPALITY
Feature Type: Simple
Geometry Type: Polygon

I added the same layer from my DSD15_SQLEXPRESS.gds and in the source tab is the following.
Data Type: SDE Feature Class
Database Platform: SQL Server
Server: dsd15_sqlexpress
Connection Properties: dsd15\sqlexpress
Authentication Type: Operating system authentication
Database: DSD
Version: dbo.DEFAULT
Description: Instance default version.
Feature Dataset: DSD.DBO.MUNICIPALITY
Feature Class: DSD.DBO.CURRENT_ZONING
Feature Type: Simple
Geometry Type: Polygon
Coordinates have Z values: No
Coordinates have measures: No

Why does this happen? the only thing i can think of some how i am not providing the correct UNC path.
What would be the correct UNC path?