Oracle ODBC TNS Service Name Box is Empty

I have installed the Oracle ODBC client on a system. TNSPING against the datasource works, but when I try to set up ODBC, it doesn't work. In the ODBC Driver Configuration box, the drop down for TNS Service Name is empty. I tried manually entering the name and other information, but when I got to Test Connection, I get the error:
[Oracle][ODBC][Ora]ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA.

Contrary to all the other cases I have found about this, TNSPING does work and returns the correct results. I am also using the correct ODBC version. More specifically, on this 64-bit OS I also tried the 32-bit ODBC and it has the same problem.
LVL 26
Brian BEE Topic Advisor, Independant Technology ProfessionalAsked:
Who is Participating?
slightwv (䄆 Netminder) Commented:
How many Oracle products do you have installed?
Make sure the tnsnames.ora that ODBC is using is the same as tnsping is using.

My guess is you have more than one ORACLE_HOME and the tnsnames.ora file isn't in all of them.

Which ODBC administrator tool are you using for which Oracle Bit version?

For the 32Bit Oracle Drivers you need the 32Bit ODBC admin tool and visa-versa.
Brian BEE Topic Advisor, Independant Technology ProfessionalAuthor Commented:
All I have installed in the Oracle client. I searched the entire system for tnsnames.ora and only found the one, plus a sample. This tnsnames file was copied from another server that is working.

Looking at the oracle install, the current tnsnames file is under C:\oracle\product\11.2.0\client_1\network\admin. There are no other versions in ...\product\... nor are there any other folders besides client_1 under ...\11.2.0

As for which ODBC version to use, neither of them work at the moment.
slightwv (䄆 Netminder) Commented:
"network\admin" for the client is the default location so that should be fine.

Are you sure you installed the ODBC drivers when you installed the Oracle Client?  Going from memory, ODBC isn't "checked" by default.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Brian BEE Topic Advisor, Independant Technology ProfessionalAuthor Commented:
The driver is listed in the ODBC configuration under the drivers tab. Definitely there otherwise I wouldn't be able to select it when I go to add the system DSN.

I can't remember exactly what the install was called, something like Simple Client, or the like. Anyway, the Oracle ODBC driver showed up after I did that.
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
With your manual setup in ODBC, you obviously did not set up service name or SID...
But I have no clue why you cannot use the TNSname entry. I usually run SysInternals ProcMon to monitor where files are searched in.
slightwv (䄆 Netminder) Commented:
>>something like Simple Client

You mean the Instant Client?

If so, that makes TWO Oracle installs.  The Instant Client doesn't automatically create the network\admin folder nor does it place the tnsnames.ora and sqlnet.ora files in the folder.

The Instant Client doesn't come with tnsping which makes me thing it is the two install issue I mentioned above.

>>The driver is listed in the ODBC configuration under the drivers tab.

Oracle's driver or the old Microsoft driver for Oracle?
Geert GOracle dbaCommented:
there is a mos note about installing multiple oracle clients on a 64-bit windows system
check Doc ID 795602.1
Brian BEE Topic Advisor, Independant Technology ProfessionalAuthor Commented:
Success! Was comparing setting with a server that does work and my server was missing the following system variable:
set TNS_ADMIN=C:\oracle\product\11.2.0\client_1\network\admin

There may have been two Oracle installs as it turns out. The application admin had made a previous attempt to fix this. But in the end we didn't get the proper Oracle ODBC driver (not the MS one) until I installed the INSTANT CLIENT. Then adding the missing system variable made it work.
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Be careful, Instant Client has different NLS and other settings stored in Registry directly in the Oracle key (instead of any OraHome key).
slightwv (䄆 Netminder) Commented:
TNS_ADMIN is an environment variable that points to a 'common' location for the tnsnames.ora file.

That can cause problems down the road, just like it did in this case...

I prefer to have each ORACLE_HOME/product install to have its own local copy.  Then it you delete one version, everything using the other versions still work.

I also suspect what I posted about the ODBC drivers not being installed by default was the cause of needing the Instant Client.
Brian BEE Topic Advisor, Independant Technology ProfessionalAuthor Commented:
While I don't think the second install was the cause of the problem, the information did point in the right direction. Thanks!
slightwv (䄆 Netminder) Commented:
If the second install was what actually installed the ODBC drivers, then that install needed to know where the tnsnames.ora file is located.

My preferred solution to this would have been rerun the installer from the client you already had installed and select the ODBC drivers.

Second preferred solution:
Manually create the network/admin folder under the Instant Client folder and copy a tnsnames.ora and sqlnet.ora files there.

Create a folder to hold a shared copy of the tnsnames.ora and sqlnet.ora files when using TNS_ADMIN.

Setting TNS_ADMIN and pointing it to an existing Oracle client install is probably the worst choice.

Now if you uninstall that client for whatever reason, you will break whatever is using the ODBC drivers since you have tied the two installs together.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.