topic Re: Python script runs in 10 but not 10.1 - using .odc connection in Python Questions

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

NathanHuggins — Thu, 07 Mar 2013 12:41:44 GMT

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"

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

MichaelVolz — Thu, 07 Mar 2013 13:03:59 GMT

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.

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

NathanHuggins — Thu, 07 Mar 2013 13:34:26 GMT

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).

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

MichaelVolz — Thu, 07 Mar 2013 14:09:06 GMT

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?

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

curtvprice — Thu, 07 Mar 2013 14:22:21 GMT

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.

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

MichaelVolz — Thu, 07 Mar 2013 14:53:47 GMT

Curtis:

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

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

curtvprice — Thu, 07 Mar 2013 15:07:56 GMT

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

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

MichaelVolz — Thu, 07 Mar 2013 15:18:26 GMT

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?

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

LucasDanzinger — Thu, 07 Mar 2013 15:23:34 GMT

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

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

curtvprice — Thu, 07 Mar 2013 15:24:45 GMT

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.

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

MichaelVolz — Thu, 07 Mar 2013 15:37:52 GMT

Nathan:

Are you running this as a standalone script where you explicitly determine which version of python you are running?

If so, maybe you do not realize that you are running in the 64 bit python environment, so you need to point the script to use 32 bit python where the ODBC connections are supported.

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

NathanHuggins — Thu, 07 Mar 2013 18:42:17 GMT

Solved, sort of. Thanks for all the suggestions Michael and Curtis. As you suspected it had to do with the 64-bit background geoprocessing. I was able to successfully run the script after uninstalling the 64-bit background processing and running a "repair" of ArcGIS to reestablish the 32-bit python file relationships. As you stated OLEDB connections are not supported in 64-bit processing. That raises another question. Could I have kept the 64-bit background geoprocessing and specified python to run in 32-bit mode?

Michael: ...so you need to point the script to use 32 bit python where the ODBC connections are supported.

Does anyone have a simple example of how specify that a script runs in 32-bit?? Or do you just change the Windows file associations?? My script will be running as a scheduled task that launches a .py file.

Thanks again for the all the help!

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

MichaelVolz — Thu, 07 Mar 2013 19:00:07 GMT

Nathan:

Here is a portion of a bat file that I call from a scheduled task that calls a python sxcript.

@echo off
echo Start time: %time% > Address_Research_Data_Update.log

echo ------------ Update time: %time%
echo ------------ Geocoding Parcel_Publish with Address_Data -------------
echo ------------ Geocoding Parcel_Publish with Address_Data ------------- >> Address_Research_Data_Update.log
c:\Python27\ArcGISx6410.1\python.exe Geocode_Address_Data.py >> Address_Research_Data_Update.log
This calls the 64 bit python version

I could change the last line to
c:\Python27\ArcGIS10.1\python.exe Geocode_Address_Data.py >> Address_Research_Data_Update.log
and I would be using the 32 bit python version which would support the odc connections.

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

NathanHuggins — Thu, 07 Mar 2013 19:44:23 GMT

Michael,

Thanks for the example. I don't have much experience writing bat files, but I do see where you could specify Python to use the 32-bit .exe. Going forward it might be something I will have to look into utilizing more.

Thanks again for all the information.

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

ChrisFox3 — Thu, 07 Mar 2013 23:03:36 GMT

Alternatively you could do something like this, inside your action set the program/script to path of the 32 bit python exe and in the Add arguements set it equal to the path to the python script. You could also then add additional arguements if your python script takes arguements.

[ATTACH=CONFIG]22457[/ATTACH]

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

MichaelVolz — Fri, 08 Mar 2013 11:43:02 GMT

Thanks Chris. I did not realize you could use the Program/script parameter to call the python executable file and the Add arguments parameter to call the specific python script. I will see how this setup works for simple scripts where I just need to make a call to a single python script.

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

curtvprice — Sat, 11 Dec 2021 13:42:14 GMT

Alternatively you could do something like this, inside your action set the program/script to path of the 32 bit python exe and in the Add arguements set it equal to the path to the python script.

I usually use a .bat file driver with scheduled tasks so I can capture any error messages from stderr, in case something goes wrong at the system level, or the Python script writes something to stderr:

C:\Python27\ArcGIS10.1\python.exe Geocode_Address_Data.py ^
  >> Address_Research_Data_Update.log 2>&1

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

RandyKreuziger — Fri, 08 Mar 2013 16:01:38 GMT

Solved, sort of. Thanks for all the suggestions Michael and Curtis. As you suspected it had to do with the 64-bit background geoprocessing. I was able to successfully run the script after uninstalling the 64-bit background processing and running a "repair" of ArcGIS to reestablish the 32-bit python file relationships. As you stated OLEDB connections are not supported in 64-bit processing. That raises another question. Could I have kept the 64-bit background geoprocessing and specified python to run in 32-bit mode?

Does anyone have a simple example of how specify that a script runs in 32-bit?? Or do you just change the Windows file associations?? My script will be running as a scheduled task that launches a .py file.

Thanks again for the all the help!

If OLEDB connections are not support with 64-bit processing what happens when ESRI puts out a 64-bit only version of ArcGIS desktop?

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

MichaelVolz — Fri, 08 Mar 2013 17:17:44 GMT

That is the same question I was thinking about when ESRI does upgrade desktop to 64 bit.

Does anyone from ESRI have any details on this subject moving forward?

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

KevinHibma — Fri, 08 Mar 2013 18:07:00 GMT

I dont have detailed information on the OLEDB story but I do know we're working to support them in 64bit to some degree (I dont know if thats 100% of what we did in 32bit or just some part of that full support).

In Server 10.2 we've made progress and you can use it as a datasource (Server 10.1 you cannot).
I'm still working on the 64bit Background story.

So: when we move to 64bit (all products), it will hopefully be a none issue (ie, we're working that out right now in the 64bit products we have)