Error when Connecting with oracle from SSMA 2005 for Oracle?

Question

I like to migrate the data from Oracle9i Enterprise Edition Release 9.2.0.8.0. This oracle is running in remote system. I can only communicate with server through Nortel VPN tool. I have installed SSMA 2005 in my system.

Aim: Import the all the table along with records from oracle(remote) to my sql server(local)

When I am connecting with oracle using SSMA 2005 tool, i am receiving following error

Connection to Oracle failed.
System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

Please help me how to avoid this and tell me how to achieve my aim.

Answer 1

You first have to install the Oracle 9.2.0.8 client on the machine you are working from. Then you have to configure your TNSNames or ONames in the client. Your Oracle DBA should be able to provide you with this information. Once you have done that, you should be able to TNSPING the Oracle instance from the command prompt. Unless you can do this step successfully, you won't be able to connect to Oracle with the other tools.

If you are working on a 64 bit machine then you have to use the 10g 64 bit client, there is no 9i client for 64 bit Windows. The steps beyond installing are essentially the same. If the application you are trying to use to connect to Oracle installs into the C:\Program Files (x86)\ path on 64 bit machine, you will have to change the install path to something that doesn't have the () in it, like c:\Program Files_x86\ due to a known bug in the Oracle drivers that has yet to be fixed to my knowledge.

The key is after installing and configuring the Oracle Client, you need to be able to TNSPing the Oracle SID from the command line. If you can't do that, you can't connect from anything else.