Link to home
Avatar of Evan Hines
Evan HinesFlag for United States of America

asked on

ODBC Access to PowerSchool (Oracle)

I am the system admin for my school's PowerSchool (student information system) server.  The underlying database of this PowerSchool system is an Oracle database.  I am in need to gaining read-only ODBC access from my technician workstation (rather than from the server).  All attempts to be able to properly configure the DSN from my workstation fail when I am unable to find the proper ODBC drivers.

I have read a few articles on EE describing the process, but I can't seem to find the proper software I need to install the proper driver. Do I have to install the full server locally to get the proper driver installed?

My client machine is a Windows 7 Professional 64-bit machine.  I can connect up to the database directly while on the server, just not from a client machine.
Avatar of Rindbaek
Rindbaek
Flag of Denmark image

i would guess that you need to install the odbc driver. You can download it from oracle technology site:
http://www.oracle.com/technetwork/database/windows/downloads/index-096177.html
btw no you dont need to install a full server on your desktop (license wise that NOT the way to do it). If you need the tools you could just install the database client software.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I would suggest the Instant Client.

You will need two downloads:
Instant Client Package - Basic
Instant Client Package - ODBC

http://www.oracle.com/technetwork/topics/winx64soft-089540.html


Unzip both files into the same folder then run the ODBC install BAT file.  Going from memory that it is a BAT file.  It might be a EXE.  It's been a while since I installed ODBC.  The readme that comes with it tells you what you need to do.
Avatar of Evan Hines

ASKER

Thanks for the suggestions.  I have attempted both suggestions but I guess I'm not doing something correctly.  With the Instant Client, I read the README file for ODBC, but it just says to make sure the Instant Client is deployed before running the ODBC install script.  How do you deploy it?  I have attempted to run all 3 provided applications but nothing seems to work and that README file is useless.

I have also tried to modify the PATH variable, in hopes the driver somehow installed, but I am unable to add a System DSN because the Oracle driver is not there.  

What am I missing?  (I'm sure it's just something simple)
>>How do you deploy it?

For the Instant Client, just unzip the two files into the same folder.

I forgot to add:  You should manually create a network/admin folder under the folder where you unzipped the files.  Then copy the tnsnames.ora file from the database server into the network/admin folder.

I was able to find a machine where I have the ODBC Instant Client installed after all.

Check out the file that should be in that folder: ODBC_IC_Readme_Win.html

Looks like the important file is: odbc_install.exe

>>but I am unable to add a System DSN because the Oracle driver is not there.  

Make sure you are running the correct bit version for ODBC configs.  On 64Bit Windows there are two, a 32Bit and 64Bit.  The 'default' one from control panel should be 64 bit.

http://support.microsoft.com/kb/942976
Thanks for the follow up.  I have added the network/admin folder to the unzipped folder, added the tnsnames.ora file from the server to that folder location.  Then I opened the read me file and continued to follow the instructions there.  I changed the PATH variable and created the TNS_ADMIN environmental varible and pointed it to the path of the .ora folder.  

From that point it appears that things should be fine, so I use the Control Panel > Administrative Tools > Data Sources (ODBC), it still shows nothing.  So I follow the Microsoft support link provided and went to the SysWOW64 folder and try to create a new data source that way, but things still didn't work.

I can see that there is the Microsoft ODBC for Oracle driver but that doesn't seem to work and just generates the error "The Oracle(tm) client and networking components were not found.  These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3 (or greater) client software installation."

It still appears that the Oracle instant client install didn't work.  

Other thoughts?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
'Run as Administrator' was the part I was omitting.

Thank you for all your help.  This has been a most informative venture for me.  I have figured out that you need the proper Oracle drivers for the bit-level of the application you are trying to run.  So in my case, I wanted to use Excel or Access (32-bit) to access the database.  But even though my computer was a 64-bit device, I needed to install the 32-bit drivers to get the connection to work on a 32-bit ODBC application.