Microsoft ODBC Driver ORA-12154 could not resolve service name from VB

My SQLNET.ORA looks like this

  LOG_FILE_CLIENT = sqlnet.log
  AUTOMATIC_IPC = OFF
  NAMES.DIRECTORY_PATH = TNSNAMES
  SQLNET.AUTHENTICATION_SERVICES= (NTS)
  LOG_DIRECTORY_CLIENT = c:\Oracle\Ora81\logs
  SQLNET.CRYPTO_SEED = 4fhfguweotcadsfdsafjkdsfqp5f201p45mxskdlfdasf
  TNSPING.TRACE_LEVEL = 4

The mystery deepens because I have also tried the following:

1) In VB, do ChDir "C:\Oracle\Ora81\Network\Admin" (that's off my Oracle Home)
    cn.Open "DSN=MYDB"

Same problem

2) Change TNSNAMES.ORA to use a SERVICE_NAME instead of a SID

 DBD =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = mydb.mydomain.com)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = MYDB.mydomain.com)
     )
   )

Same problem. (It works either way through SQL*Plus or TNSPING)

3) Change the line in SQLNET.ORA

  SQLNET.AUTHENTICATION_SERVICES= (none)

Same Problem

4) In VB, change to a DSN-Less connection
   cn.Open "Provider=MSDASQL;DRIVER={Microsoft ODBC for ORACLE};UID=user;PWD=password;Server=DBD"

Same problem

5) Test the DSN using the Oracle ODBC Test Program.

Same problem

6) Create a DSN using the Oracle ODBC Driver

This works fine from both VB and the Oracle ODBC Test Program

Make sure you are using the latest MDAC version which will give you the latest bug fixes for the Microsoft ODBC Driver.
In reality Oracle driver has not been licensed to Microsoft. So Microsoft Oracle OLEDB driver is a bridge from OLEDB client to Oracle DB Network lib. so you may have to install Network lib.

I don't think I need to install the Oracle Network lib. Not even sure what that is.

If it is NET80, that is obviously already installed (otherwise SQL*Plus would not work, neither would the Oracle ODBC driver).

I am using MDAC 2.6 (Build 6526). Will check if anything is newer. I begin to suspect that this may be the answer. Can't think of why else the Microsoft ODBC driver would fail when the Oracle ODBC driver works.

I had this problem with an ASP application.  The MS ODBC would work and stop.  Frustrating.  If I remember correctly, I had to re-install MDAC every single F*(&% time
and then re-boot.  It Sucked.

I tried using Oracle ODBC, but I could not find any documenatation

I switched to Oracle's 0040.  VB COM component.  Don't know how much code you would have to re-write to switch.

You could try a 3rd party driver from someone like Merant.

To mjzalewski...
A bit of an open door I guess: you mentioned the solution yourself: use the Oracle ODBC driver. You might be able to download it from OTN (http://otn.oracle.com/ or http://otn.oracle.com/software/tech/windows/odbc/content.html).
I've experienced the same problems. They were solved by using the Oracle ODBC Driver.
Should anyone comment on the fact that Microsoft's ODBC Driver for Oracle is better than Oracle's: it was!
Good luck and Regards,
Portolanus

Hi,
You cannot use a higher version client to a lower version database server. Try with same version of oracle 8.1.6

Bye,
Moorthy.

ssaravanamoorthy

Actually, you cannot use a lower version client to connect to a higher version database. According to the documentation, 8.1.7 should be able to connect to anything above 7.2. Also, if that was correct, then why would the Oracle driver work and also SQL*Plus, TNSPING, etc.

I re-installed from Oracle 8.1.6, and still have the same problem. But the version mismatch might be on the right track. The error message changed however. Now, from Oracle ODBC Test, I get

  SQLSTATE: NA00
  Native Error Code:0
  Driver Message:[Microsoft][ODBC driver for Oracle] [Oracle]

Visual BASIC gives something very similar

  Run-time error '-2147467259 (80004005)':
  [Microsoft][ODBC driver for Oracle][Oracle]

(In other words, now it does not give 'ORA-12154 could not resolve service name' any more. Now the message from the ODBC driver is emtpy).

I use PVCS Dimensions, which requires Oracle 8.1.7, but is installed in a seperate ORA_HOME.

Microsoft driver for Oracle does have a lot of  probelms!!! Can you try out the same with Oracle driver for Oracle? As you have already (Re)installed Oracle this driver would also be available.

My problem is not with the Oracle driver. It is with the Microsoft Driver.

Everything seems to work with the Oracle driver. But the software I am developing for must use the Microsoft Driver.

One more thing I found. In the Oracle ODBC Test program, there is a help option 'Making sure SQL*NET is OK'. I know SQL*NET is OK, because TNSPING and SQLPLUS work just fine. But the help screen says

1) The file ORAWIN95\ORAINST\win95.rgs or ORANT\ORAINST\nt.rgs should show "Sql*net" as being installed. If you don't see this, you need to install it from the \NT_X86 or \WIN32 or \WIN95 directory need to your CD roms, or if you can't find it, you may need to order it (ask for "Sqlnet for win 95" or "Sqlnet for win NT" ).

I do not have eitehr a win95.rgs or nt.rgs anywhere on the system. I *do* have a file named "Oracle 816 Production.rgs", but it has 0 bytes.

Hai mjzalewski,

As I said before, and anand_2000v agreed (He's #10 expert at the moment, I am new here), there's a time to persist, and there's a time to let go.
Drop the Microsoft ODBC-driver and use the Oracle ODBC-driver.

I'm a bit puzzled: How come that "But the software I am developing for must use the Microsoft Driver"?
Do you use Developing software that refuses to connect through Oracle's ODBC-driver?
Do you have a manager who, not behindered by any specific technical knowledge ;-), has a strong personal oppinion?
Any other specific reason?

With curious regards,
Portolanus.

I couldn't agree more with you Port, but still supposing "a manager who, not behindered by any specific technical knowledge ;-), has a strong personal oppinion?" is true we cannot blame the asker....:)
 to try out the Asker's angle once again, Oracle recomends that you get your latest MDAC upgrade(whatever that is- some Microsoft speak I'm sure). That might just solve your problem.

When on path does not provide the answer, you must choose a different path.  Go with the Oracle Driver, you'll have to recode the app.  Put all the data access code in a class file or build a COM component (dll) to access the data through the app.

Then when this stuff happens you can port the app over quickly and painlessly if the SQL statements are pretty much the same.

I also have spoken with other Oracle users who switched from Microsofts ODBC to Oracles/a 3rd parties ODBC and avoided the problem you are having.  Each would get the driver to work but the problem would re-occur.  

I gave up after a week of the microsoft driver kept failing wiht oracle 8i.  Oracle driver and 0040 never failed.

Portolanus:

I am developing a piece to a working program. So the Microsoft driver works in the current version of production, but does not work on my system. Also, the Microsoft driver supports a curious feature -- the DBA can specify the production user and password in the DNS entry by entering 'user/password' from the ODBC Administration applet where the Microsoft ODBC for Oracle Setup window prompts for 'User Name'. You can't do the same from the Oracle driver. And there are other differences.

The system has been working in production (with the Microsoft driver) since 1999. I am changing only one piece, and cannot regression test the entire application.

If I were developing a new system, then yes, I could say 'Just install the Oracle ODBC driver, and we can deal with how to set the password through a .INI file or something'. But since the bulk of the system is in place and will not be changed, switching the driver is not an option.

I know the problem is difficult. That's why I gave it 400 points. An answer that says 'just use the Oracle driver' is not worth 400 points. I appreciate the suggestion (which has been offered by several of the responders), but in my case, it does not solve my particular problem.

In the meantime, I have completed most of the development, doing as much of the actual programming and unit testing as I could with the Oracle driver. The QA testers will use a test system, which has the Microsoft driver installed (the Microsoft driver works there, but my coding changes may not ...)

cdevx gets the bannana.

It was the registry entries described in

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledboraprovider_7.asp

Mine were set up for Oracle 7.x on Windows NT. So if anyone else has this problem, it might be fruitful to check the registry as described in the anser from cdevx