AnsweredAssumed Answered

Difficulty running script on server w/ Oracle OLE DB connection; Error 000732

Question asked by ScottEvans_CCG on Aug 21, 2017
Latest reply on May 18, 2018 by ScottEvans_CCG

Like • Show 0 Likes0
Comment • 10

I recently wrote a python script to take CAMA data from an Oracle database in the Assessors' office and update a table on our enterprise geodatabase; basically just taking property ownership information from one table and updating it daily in another so we can consume it in our GIS system. At first, I tested on my desktop machine where it ran just fine. However, when I went to test it on our GIS server, that's where the trouble began. It seems to not be able to connect to the Oracle database via the script, as I presented with the 'Error: 000732: < dataset name > does not exist or is not supported' error when running any tool that requires a connection as an input (e.g MakeTableView, CopyRows, TableToGeodatabase, etc). Definitely has me stumped because I am able to connect to the database manually via an OLE DB connection in both ArcCatalog and in ArcMap; however, when run in IDLE it fails.

At first I though it might be a 32-bit vs 64-bit issue. The desktop machine uses the 32-bit flavor of python in IDLE. Thought perhaps the server was forcing the script to run using the 64-bit, but after running this helpful little bit of code:

import sys
print sys.version

it appears as though the server is running 32-bit as well.

As far as I know the Oracle database we are connecting to is 11g. However, both the server and desktop machines are using the 12c Release 2 (12.2.0.1.0) 32-bit flavor of the Oracle client because the 11g client does not does support Windows 10. Currently, my desktop machine is running 10.3.1 and the server machine is running 10.4.1, w/ python versions 2.7.8 and 2.7.10, respectively.

The only thing I haven't tested is installing the 64-bit version of the client software on the server side. Even though IDLE tells me it's running on the 32-bit side of things, I have a suspicion that somehow the connection to the database needs a 64-bit component ...

No one else has this question

Outcomes

George_Thompson-esristaff

Aug 21, 2017 5:59 AM

Yes you are correct that you need to have the 64 bit client installed for the Server component.

You also mentioned that the Server was returning 32 bit Python as default. It should be the 64 bit package for Server. Here is an article that may help: FAQ: Why do Python scripts fail on a machine with both ArcGIS for Server and Desktop installed?

1 person found this helpful

Actions

ScottEvans_CCG Aug 21, 2017 6:58 AM

Alright I shall obtain the 64-bit client for the server. Do you think it would be wise to uninstall the 32-bit version of the client or could they be run simultaneously? Reason I ask is sometimes I need to manually view the table in Catalog or ArcMap, and that would require the 32-bit.

Also, I am reluctant to fiddling around with the bit version of python on the server machine ... I currently have about 8 scripts running automatically in Task Scheduler. Some of them run hourly. My point is: they all run just fine. If they break, then I am in even more of a jam.

Actions

George_Thompson-esristaff

@ ScottEvans_CCG on Aug 21, 2017 7:01 AM

Yes, just install the 64-bit client for the Server. I would leave the 32 bit client also. You can have both on your machine (I do for ArcMap/Pro).

I understand the concern with changing Python. Install the client and see if the results are different when running the script.

Actions

ScottEvans_CCG @ George_Thompson-esristaff on Aug 21, 2017 10:32 AM

Alright so now I have both 32-bit and 64-bit client installed. Tried running it in IDLE using the 32-bit version of Python; same error. Next, I tired running it using the 64-bit version of Python; same results ... ERROR 000732.

I will note that I tried this in IDLE (by manually navigating to where python is installed on the 64-bit side), through CMD line (again calling out the 64-bit version), and in Task Scheduler ...

Actions

mvolz47 Aug 21, 2017 11:00 AM

Scott:

Are you saying that there is no version of Oracle 11g that will work on a Windows 10 OS?

Actions

ScottEvans_CCG Aug 21, 2017 11:10 AM

As far as I know, 11g is not supported with Windows 10. Tried installing and was presented with an error.

For reference, Oracle 11g R2 installation on windows 10 & Error when trying to Install Oracle Client on W... | Oracle Community. Honeslty, I am not an Oracle guy (nor is anyone at my organization ...)

But like I said, I've installed the 32-bit client software for 12c and was able to connect in ArcCatalog no problem. The only issue I have is running that script on the server itself ....

Actions

mvolz47 Aug 21, 2017 11:16 AM

Can you go into more detail on how you setup the OLE DB connection on the desktop as well as the server?

Actions

ScottEvans_CCG Aug 21, 2017 12:29 PM

Sure, no problem!

First I installed the client; just downloaded the zip file from the Oracle website, extracted it and placed it on the C:/ drive. Opened up the extracted folder and ran the .exe file to install the client software. Ran through all of the defaults except where it asked for Installation Type .... chose Administrator (other options included: InstantClient, Runtime, and Custom). Everything installed just fine.

Next, I opened up ArcCatalog and chose 'Add OLE DB Connection'. Selected the 'Micosoft OLE DB Provider for Oracle' option under the Provider tab. Under the Connection tab I provided the server name, using this format: <servername>/<instance> and plugged in the username/password provided to us. Hit 'Test Connection' and it succeeded. Note we kept the default settings for all of the options under the Advanced and All tabs. After that we hit OK and were to able to connect.

I used this same process on both the server side as well as the desktop side.

Actions

jamesfreddyc Sep 7, 2017 7:16 AM

We've been doing this for several years by simply keeping it all in procedural code and a linked server instance (you're just managing non-spatial data). Have the assessor setup a view, you create a linked server instance on your db and then you can just access the attributes as needed with a Stored Procedure or other.

1 person found this helpful

Actions

ScottEvans_CCG May 18, 2018 5:30 AM

After almost 3/4th's of a year, I finally got this figured out. I actually had put this task on the back-burner, as I was able to run my script just fine on a local machine. However, in an effort to get all of our automated tasks running in a server environment, I decided once again to explore the issue I was having.

Here's what I did: made sure both the 32-bit and 64-bit Oracle clients were installed on the server. I had both installed before under a generic account but I actually had to reinstall both because our organization switched some security policies. Next (and I think this was the task that fixed the issue) I made sure that the 'Path' system variable under Environmental Variables was pointed to the following folders:

D:\app\client\<username>\product\12.2.0\client_2\bin

D:\app\client\<username>/product\12.2.0\client_1\bin

Obviously 2 folders for the 32 and 64-bit flavors of the client. Now I am successfully able to open the OLE DB connection on the server in arcmap as well as the nightly python script.

Actions

10 Replies

George_Thompson-esristaff Aug 21, 2017 5:59 AM
Mark Correct
Correct Answer
Yes you are correct that you need to have the 64 bit client installed for the Server component.

You also mentioned that the Server was returning 32 bit Python as default. It should be the 64 bit package for Server. Here is an article that may help: FAQ: Why do Python scripts fail on a machine with both ArcGIS for Server and Desktop installed?
1 person found this helpful
Like • Show 0 Likes0
Actions
ScottEvans_CCG Aug 21, 2017 6:58 AM
Mark Correct
Correct Answer
Alright I shall obtain the 64-bit client for the server. Do you think it would be wise to uninstall the 32-bit version of the client or could they be run simultaneously? Reason I ask is sometimes I need to manually view the table in Catalog or ArcMap, and that would require the 32-bit.

Also, I am reluctant to fiddling around with the bit version of python on the server machine ... I currently have about 8 scripts running automatically in Task Scheduler. Some of them run hourly. My point is: they all run just fine. If they break, then I am in even more of a jam.
Like • Show 0 Likes0
Actions
- George_Thompson-esristaff @ ScottEvans_CCG on Aug 21, 2017 7:01 AM
  Mark Correct
  Correct Answer
  Yes, just install the 64-bit client for the Server. I would leave the 32 bit client also. You can have both on your machine (I do for ArcMap/Pro).
  
  I understand the concern with changing Python. Install the client and see if the results are different when running the script.
  Like • Show 0 Likes0
  Actions
  - ScottEvans_CCG @ George_Thompson-esristaff on Aug 21, 2017 10:32 AM
    Mark Correct
    Correct Answer
    Alright so now I have both 32-bit and 64-bit client installed. Tried running it in IDLE using the 32-bit version of Python; same error. Next, I tired running it using the 64-bit version of Python; same results ... ERROR 000732.
    
    I will note that I tried this in IDLE (by manually navigating to where python is installed on the 64-bit side), through CMD line (again calling out the 64-bit version), and in Task Scheduler ...
    Like • Show 0 Likes0
    Actions
mvolz47 Aug 21, 2017 11:00 AM
Mark Correct
Correct Answer
Scott:

Are you saying that there is no version of Oracle 11g that will work on a Windows 10 OS?
Like • Show 0 Likes0
Actions
ScottEvans_CCG Aug 21, 2017 11:10 AM
Mark Correct
Correct Answer
As far as I know, 11g is not supported with Windows 10. Tried installing and was presented with an error.
For reference, Oracle 11g R2 installation on windows 10 & Error when trying to Install Oracle Client on W... | Oracle Community. Honeslty, I am not an Oracle guy (nor is anyone at my organization ...)

But like I said, I've installed the 32-bit client software for 12c and was able to connect in ArcCatalog no problem. The only issue I have is running that script on the server itself ....
Like • Show 0 Likes0
Actions
mvolz47 Aug 21, 2017 11:16 AM
Mark Correct
Correct Answer
Can you go into more detail on how you setup the OLE DB connection on the desktop as well as the server?
Like • Show 0 Likes0
Actions
ScottEvans_CCG Aug 21, 2017 12:29 PM
Mark Correct
Correct Answer
Sure, no problem!

First I installed the client; just downloaded the zip file from the Oracle website, extracted it and placed it on the C:/ drive. Opened up the extracted folder and ran the .exe file to install the client software. Ran through all of the defaults except where it asked for Installation Type .... chose Administrator (other options included: InstantClient, Runtime, and Custom). Everything installed just fine.

Next, I opened up ArcCatalog and chose 'Add OLE DB Connection'. Selected the 'Micosoft OLE DB Provider for Oracle' option under the Provider tab. Under the Connection tab I provided the server name, using this format: <servername>/<instance> and plugged in the username/password provided to us. Hit 'Test Connection' and it succeeded. Note we kept the default settings for all of the options under the Advanced and All tabs. After that we hit OK and were to able to connect.

I used this same process on both the server side as well as the desktop side.
Like • Show 0 Likes0
Actions
jamesfreddyc Sep 7, 2017 7:16 AM
Mark Correct
Correct Answer
We've been doing this for several years by simply keeping it all in procedural code and a linked server instance (you're just managing non-spatial data). Have the assessor setup a view, you create a linked server instance on your db and then you can just access the attributes as needed with a Stored Procedure or other.
1 person found this helpful
Like • Show 0 Likes0
Actions
ScottEvans_CCG May 18, 2018 5:30 AM
Mark Correct
Correct Answer
After almost 3/4th's of a year, I finally got this figured out. I actually had put this task on the back-burner, as I was able to run my script just fine on a local machine. However, in an effort to get all of our automated tasks running in a server environment, I decided once again to explore the issue I was having.

Here's what I did: made sure both the 32-bit and 64-bit Oracle clients were installed on the server. I had both installed before under a generic account but I actually had to reinstall both because our organization switched some security policies. Next (and I think this was the task that fixed the issue) I made sure that the 'Path' system variable under Environmental Variables was pointed to the following folders:

D:\app\client\<username>\product\12.2.0\client_2\bin
D:\app\client\<username>/product\12.2.0\client_1\bin

Obviously 2 folders for the 32 and 64-bit flavors of the client. Now I am successfully able to open the OLE DB connection on the server in arcmap as well as the nightly python script.
Like • Show 0 Likes0
Actions

GeoNet

The Esri Community

Difficulty running script on server w/ Oracle OLE DB connection; Error 000732

Outcomes

Related Content

Recommended Content