mjzalewski
asked on
Microsoft ODBC Driver ORA-12154 could not resolve service name from VB
I am making changes to a legacy VB 6 application. My VB code looks like this:
Dim cn As New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Open "DSN=MYDB"
I get the following error (although the code is working in the legacy production system)
Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC driver for Oracle][Oracle]ORA-12154: TNS:could not
resolve service name
My DSN entry (it's a System DSN, using the Microsoft ODBC Driver for Oracle) looks like this:
Data Source Name: MYDB
Description: My Database
User Name: user/password
Server: DBD
My TNSNAMES.ORA file looks like this:
DBD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mydb.mydomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DBD)
)
)
I am trying to run this using client version 8.1.7 on Windows 2000, and the database server has 8.1.6
I can do TNSPING DBD and it works fine.
I can connect to the database using SQL Plus without problems.
What could my problem be?
Dim cn As New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Open "DSN=MYDB"
I get the following error (although the code is working in the legacy production system)
Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC driver for Oracle][Oracle]ORA-12154: TNS:could not
resolve service name
My DSN entry (it's a System DSN, using the Microsoft ODBC Driver for Oracle) looks like this:
Data Source Name: MYDB
Description: My Database
User Name: user/password
Server: DBD
My TNSNAMES.ORA file looks like this:
DBD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mydb.mydomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DBD)
)
)
I am trying to run this using client version 8.1.7 on Windows 2000, and the database server has 8.1.6
I can do TNSPING DBD and it works fine.
I can connect to the database using SQL Plus without problems.
What could my problem be?
anand_2000v
can u post the info about ur sqlnet.ora file
ASKER
My SQLNET.ORA looks like this
LOG_FILE_CLIENT = sqlnet.log
AUTOMATIC_IPC = OFF
NAMES.DIRECTORY_PATH = TNSNAMES
SQLNET.AUTHENTICATION_SERV
LOG_DIRECTORY_CLIENT = c:\Oracle\Ora81\logs
SQLNET.CRYPTO_SEED = 4fhfguweotcadsfdsafjkdsfqp
TNSPING.TRACE_LEVEL = 4
The mystery deepens because I have also tried the following:
1) In VB, do ChDir "C:\Oracle\Ora81\Network\A
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_SERV
Same Problem
4) In VB, change to a DSN-Less connection
cn.Open "Provider=MSDASQL;DRIVER={
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
LOG_FILE_CLIENT = sqlnet.log
AUTOMATIC_IPC = OFF
NAMES.DIRECTORY_PATH = TNSNAMES
SQLNET.AUTHENTICATION_SERV
LOG_DIRECTORY_CLIENT = c:\Oracle\Ora81\logs
SQLNET.CRYPTO_SEED = 4fhfguweotcadsfdsafjkdsfqp
TNSPING.TRACE_LEVEL = 4
The mystery deepens because I have also tried the following:
1) In VB, do ChDir "C:\Oracle\Ora81\Network\A
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_SERV
Same Problem
4) In VB, change to a DSN-Less connection
cn.Open "Provider=MSDASQL;DRIVER={
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.
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.
ASKER
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.
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.
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
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.
You cannot use a higher version client to a lower version database server. Try with same version of oracle 8.1.6
Bye,
Moorthy.
ASKER
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.
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.
ASKER
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
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.
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
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.
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.
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.
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.
ASKER
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 ...)
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 ...)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Cool! Thanks