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

xrati <> wrote in message news:<d536ju01aa9dsocj4aj3vn24d2o5t9suf7_at_4ax.com>...
> 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 8.01.07.00 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 deja.com archives
> based on ORA-12154 and SQLsetConnectAttr fail search and did not come
> up with an answer. TIA

Whenever possible, use Oracle's ODBC driver. Your Oracle ODBC driver version 8.01.07.00 is right for 8i.

If you want to use Oracle's ODBC driver, then make sure your ORACLE_HOME\bin directory is the first entry in your system path...or atleast before %System32% directory. Reason for this is that you may have an older version of Sqora32.dll hanging around in %System32% directory...specially after any Oracle upgrade. Putting ORACLE_HOME\bin before %System32% ensures that you always use latest version.

After creating your DSN, test it using Oracle's ODBC test. You can do this by launching ORACLE_HOME\ODBC\odbctst.exe or getting there from Program menu.

Error,
> ORA-12154 TNS could not resolve service name
means as exactly what it says. Oracle cannot resolve your service name. Check your tnsnames.ora file and make sure you are using right service name. If you can connect to the database from sqlplus using a service name...then you can use same service name from ODBC.

HTH
//Rauf Sarwar Received on Mon Jul 15 2002 - 20:18:08 CDT