Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> ODBC problem to many Oracle DB's, any idea why?

ODBC problem to many Oracle DB's, any idea why?

From: xrati <no email>
Date: 15 Jul 2002 12:57:08 -0500
Message-ID: <>

I have always found it very difficult to access several different Oracle Databases at our workplace over the past few years via MS access or other 3rd party apps and can't figure out why. Every 6 months or so, we get an additional server and obviously additional sids. Currently, we have oracle 8i client on all our workstations. I'm sure I understand how to create an ODBC and produce the proper tnsnames.ora file and ODBC. However, I always have trouble getting into an oracle db through MS Access (link table) or other 3rd party vendors like Crystal reports. I have problems with both the MS oracle odbc and oracle odbc's. When using the oracle client, I get a test successful connection. However, each time I try to set up my machine, I will get an error like:

ORA-12154 TNS could not resolve service name. Driver's SQLsetConnectAttr fail.

Now, I also have tried about 6 other machines at work and have the same problem. For my computer, I had deleted the odbc's and produced them again several times. I have also used 8i client to install, delete and reinstall tnsname.ora file several times without success. Then, without reason I can finally get the odbc's to work after 30 minutes. This always occurs with every new oracle server. Now, the problem is that I have to set up 50 machines with the same behavior problems. I'm not sure why our machines don't set up correctly the first time. Anyone have a clue? I spent 2 hours on one machice and was only able to get the oracle odbc driver to work, but we prefer the MS oracle ODBC.

Anyway, I have Microsoft ODBC for oracle 2.573.6019.00 and Oracle ODBC driver options on our machines. Some have had previous 6.0 client installs, which could be a problem. However, I had someone put on oracle 8i client on a W2k machine with SP2 that never had oracle client on it and still have the same problem. Any help would be appreciated. I have read several archives based on ORA-12154 and SQLsetConnectAttr fail search and did not come up with an answer. TIA Received on Mon Jul 15 2002 - 12:57:08 CDT

Original text of this message