Python script runs in 10 but not 10.1 - using .odc connection

[NathanHuggins]

I currently have a script that pulls information from a Microsoft SQL Server Table View and exports it to a file geodatabase using an .odc connection. This script will run fine in version 10.0, but will error out using version 10.1. Giving the error message ERROR 000732: ... does not exist or is not supported. The strange thing is that I can build a model in ArcCatalog that will run successfully, then export that model to python, and that script will fail. So basically the geoprocessing tool will run with ArcCatalog open, but not as a python script.

Has ESRI changed something with regards to using .odc connections and MS SQL Server Views?? Or does anyone have a workaround? Below is some example code. Again runs fine in 10 but not in 10.1.

# Local variables: dbo_cosSewerManholes = "Database Connections\\Han8Live.odc\\dbo.cosSewerManholes" Infor_gdb = "D:\\Temp\\Infor.gdb"  # Process: Table to Table arcpy.TableToTable_conversion(dbo_cosSewerManholes, Infor_gdb, "manholes_view") print "done" 

[MichaelVolz]

I believe you will need to go to the explicit path of your odc connection.

dbo_cosSewerManholes = "Database Connections\\Han8Live.odc\\dbo.cosSewerManholes"

example - dbo_cosSewerManholes = "c:\\Users\\"some_name"\\AppData\\Roaming\\ESRI\\Desktop10.1\\ArcCatalog\\Han8Live.odc\\dbo.cosSewerManholes"

You would need to investigate the path for your exact system.  This is what I needed to change in my python scripts just for sde connections where in 10.0 I was able to use Database Connections at the start of my connection string.

[NathanHuggins]

Thanks for the suggestion. I just ran the script with the specific .odc path.

dbo_cosSewerManholes = "C:\\Users\\nhuggins\\AppData\Roaming\\ESRI\\Desktop10.1\\ArcCatalog\\Han8Live.odc\\dbo.cosSewerManholes"

Script still fails to run. Exact error message below.

Traceback (most recent call last):
File "C:\Users\nhuggins\Desktop\aa.py", line 20, in <module>
arcpy.TableToTable_conversion(dbo_cosSewerManholes, Infor_gdb, "manholes_view")
File "C:\Program Files (x86)\ArcGIS\Desktop10.1\arcpy\arcpy\conversion.py", line 1904, in TableToTable
raise e
ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000732: Input Rows: Dataset C:\Users\nhuggins\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog\Han8Live.odc\dbo.cosSewerManholes does not exist or is not supported
Failed to execute (TableToTable).

[MichaelVolz]

I have not tested .odc connections in ArcGIS v10.1, but these connections are not available by default in ArcCatalog like they were in v10.0.  You made need to enable an additional toolbox or extension for the .odc connections to work.

Can you try manually connecting to the same datasource in ArcCatalog?

[curtvprice]

I currently have a script that pulls information from a Microsoft SQL Server Table View and exports it to a file geodatabase using an .odc connection. This script will run fine in version 10.0, but will error out using version 10.1.

Have you tried running the python script in the foreground? If you installed x64 geoprocessing I believe many ODBC drivers will not work in 64 bit python.

[MichaelVolz]

Curtis:

How can you tell if a python script is running in the foreground vs the background?

[curtvprice]

Curtis:

How can you tell if a python script is running in the foreground vs the background?

This is spelled out in the help, with pictures:
Arc 10.1 Help: Foreground and background processing

On review, I don't think x64 geoprocessing is probably Nathan's problem; it's probably the new setup with database connections in 10.1, which has changed quite a bit. Hopefully someone with more expertise will chime in.

Nathan, if you haven't, I suggest you read this:
Arc 10.1 Help: What's new for databases in ArcGIS 10.1

[MichaelVolz]

Curtis:

What if I am running the python script from a Windows Server 2008 Scheduled Task?  I do not believe I have the option of running the script in the background, as by default, the script runs in the foreground.  As such, I thought geoprocessing at v10.1 can be run in the 64 bit environment, but if I cannot force the script to run in the background it will still run in the 32 bit environment.

So even though I have access to a 64 bit environment, I cannot make use of this environment with a scheduled task because it can only run in the foreground.

Am I correct with this assessment?

[LucasDanzinger]

Michael-

If you run it in task scheduler, it will open with whatever the file association is, so it is possible that it is opening with the 64 bit version of Python. Just to be safe, it is a good idea to write a batch file that points to the 32 bit version of Python and then runs your script in that version.

-Luke

[curtvprice]

Curtis:

What if I am running the python script from a Windows Server 2008 Scheduled Task?  I do not believe I have the option of running the script in the background, as by default, the script runs in the foreground.  As such, I thought geoprocessing at v10.1 can be run in the 64 bit environment, but if I cannot force the script to run in the background it will still run in the 32 bit environment.

So even though I have access to a 64 bit environment, I cannot make use of this environment with a scheduled task because it can only run in the foreground.

Am I correct with this assessment?

Foreground and background only really makes sense when running in the Desktop application environment, ie ArcCatalog/ArcMap. In that situation, foreground is 32 bit and background is 64 bit (if you've installed the 10.1 Sp 1 patch).

If you want to run 64 bit in a standalone Python script, import arcpy in a 64 bit Python session. You will only have 64 bit python available if you if you have installed ArcGIS Server --or-- the Desktop x64 geoprocessing patch for ArcGIS 10.1 SP 1.