Cannot connect to Oracle 9 (after 10g Client install)

[Former Member]

I have Crystal Report XI (11.0.0.895 Developer Version).

I have only been connecting to SQL and Oracle 9 databases, however one of our Oracle servers has been upgraded to 10g:- I can now access all my SQL databases, and the Oracle 10g databases without any problems however I can no longer connect to the Oracle 9 databases.

I have check my ODBC connections & settings and I tested all my connections and they all worked correctly, I have even deleted and created new settings for the Oracle 9 and 10g ODBC settings. (I have 10g and 9 client installed on my pc)

I get the following errors:-

When trying to open a report that I had previously created:-

Failed to open the connection.

Details: ORA-12154: TNS:could not resolve the connection identifier specified

[Database Vendor Code: 12154 ]

When I try and setup a new connection with my ODBC connection

Logon failed.

Details: S1000:[Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error

[Database Vendor Code: 12560 ]

..............did manage to get it to work once, I unistalled Crystal Reports and then reinstalled it - it worked for the afternoonm but I do not want to uninstall and reinstall Crystal Reports everytime I want ot make a change to a report or I reboot.

[former_member11696]

Hi Jonathan,

This is not a CR issue.

When I try and setup a new connection with my ODBC connection

Logon failed.

Details: S1000:OracleODBCOraORA-12560: TNS:protocol adapter error

Database Vendor Code: 12560

You are having issues with the Oracle client if ODBC won't work. I believe what is happening is you are mixing Oracle runtime versions and accessing the correct location of the client. I suggest you search Oracles site on how to do this and what is required to make it work and it's limitations.

And for your info Crystal Reports uses the PATH statement to find the various DB clients. So the first one in the PATH is the one CR will use. You may have to create a btach file to change this but not sure if Oracle will work without re-booting.

You may be able to connect using just the Oracle 10 client.

Only other option is to have a VM-ware image running each version or separate PC.

Thank you

Don

[Former Member]

Thanks for the reply!!

As a result I can now get round it....edit the path before I start workign on one of the database, so the path lists the database I want to access first. A bit of a pain, but it works!!

Thanks again!

[Former Member]

Hi Jonathan,

How did you get around? I am having the same problem and I was soooo frustrated. Could you show us steps-by-steps of what you did to get around this problem? Greatly appreciate.

Best regards

Tuan H.

[former_member11696]

Hi Tuan,

I believe what he did was:

On XP right click on Exporer and select Properties. Then select the Advanced tab and click on Environment Variables.

In the System you should see a PATH statement. In there you'll see the path to both the Oracle 9 bin and Oracle 10 bin folders as well as some other references to Oracle.

If you go to a cmd window and simply type in path you'll see something like this:

Microsoft Windows XP [Version 5.1.2600]

(C) Copyright 1985-2001 Microsoft Corp.

H:\>path

PATH=D:\oracle\product\10.2.0client\bin;

D:\oracle\product\10.2.0\client;

...and a bunch of other stuff.

H:\>

You can create a BATCH file to change your lines for the 9 and 10 to change places. If you want to use the Oracle 10 then put it first in line, if you want to use Oracle 9 then put it first in line.

This does/may require a re-boot for your user. You should also look at the User PATH statement, it may also need to be altered. System changes I believe take affect right away but User changes require a re-boot. Reason being the Oracle client loads on start up so to un-load it a re-boot is required.

Hope this helps. you may want to check with Oracle to see if they ahve some way of unloading and reloading different versions.

Thanks again

Don

[Former Member]

Hi,

Sure, here is what I do depending on which database I am wanting to access:-

(Running Windows XP Professional)

1. Click Start

2. Right click on My Computer, and select Properties

3. Click on the Advanced tab

4. Click on Environmental Variables

5. Bottom part of the Environmental Variables, scroll down till you see a variable called Path

6. Highlight Path and click Edit

7. Here is where I make the change depending on which Oracle Client I need to use, the first to entries here are the paths to my oracle clients:-

a. To use 10g client I have:- C:\oracle\*Ora10g\bin;C:\oracle\ora92*\bin;...........

b. To use a 9i client I have:- C:\oracle\*ora92\bin;C:\oracle\Ora10g*\bin;.........

8. I make this change before I open which ever application I am about to use i.e. Business Objects XI

Before I did this I made sure to copy and paste the full path to notepad and keep an original copy of the path settings before I made these changes just to be safe. I have never had a problem with this since, and it is not too much of a footer to do before starting work on a different database.

Hope this helps!!

[Former Member]

Thank you very much for your replies. I am currently using Windows Vista 64-bit(which causing lots of pains) and the PATH only show the 10.2.0 clientl. Here is the PATH I copied from the system.

C:\oracle\product\10.2.0\client_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\Intel\DMIX;C:\Program Files (x86)\NTRU Cryptosystems\NTRU TCG Software Stack\bin\;C:\Program Files\NTRU Cryptosystems\NTRU TCG Software Stack\bin\;C:\Program Files\Wave Systems Corp\Gemalto\Access Client\v5\;c:\Program Files (x86)\Common Files\Roxio Shared\DLLShared\;c:\Program Files (x86)\Common Files\Roxio Shared\10.0\DLLShared\;c:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\

I have removed client 9i but the driver is still there. Do you think it might have caused the problem?

Thanks again,

Tuan

[Former Member]

Yes, I believe it would cause a problem - when I did not know how to resolve it IT guys came round and uninstall both clients and then re-installed them. This did not resolve the problem I had, but it did enable me to edit the paths and thus get around it.

After saying that I am not too sure about these things myself, but I reinstall both clients again making sure to use different folders for each of them, that enabled me to change the paths.

Hope this is of help!

[Former Member]

After I uninstalled all existing Oracle instances in my Windows Vista 64-bit laptop, including registry and any oracle drivers. I download and install Oracle client 10.2.0.3 and it's working fine.

From what I experienced, ONLY Client 10.2.0.3 will work with Vista 64-bit and CRXI-R2, not even 10.2.0.4 specifically designed for Vista 64-bit

Thanks again for all your assistance

Regards,

Tuan