?
Solved

ODBC -- call failed

Posted on 2007-10-09
15
Medium Priority
?
1,914 Views
Last Modified: 2013-12-19
Additional line for error message is:

[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed (#0)

I am trying to link to Oracle tables (I have tried both Oracle 9 and Oracle 10) in MS Access, but get this error.

Any ideas?

Thanks.
0
Comment
Question by:multimodal
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 20041305
SOLUTION:
----------

This error indicates that the client PC is unable to interpret the
HOST name supplied in the data source under SERVER.  

Try and PING the host name from the DOS prompt.  If it does not respond, then
the PC does not have the host name set up in the network HOSTS file or in
the DNS server.  Seek network assistance to configure the network on the client so that the client will recognize the host name.

WORKAROUND:
------------

Use the IP address for the HOST you are trying to connect to in the
SERVER field of the data source for your connection.


0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 20041442
We have used ODBC connections to both Oracle9 and Oracle10 databases, and we have used ODBC drivers from: Oracle, Microsoft and Merant (with different applications) so it certainly can be done.

Which ODBC driver are you using?  Do you have the Oracle client software installed, or do you prefer to get this working without using the Oracle client software?  I think that is possible with the Merant driver, I'm not sure if that is possible with the Microsoft ODBC driver for Oracle or not.
0
 

Author Comment

by:multimodal
ID: 20041589
I can connect successfully by PINGing and also via SQL Plus.

If I try to connect to the data via MS Access, I get the error listed in the original message.  If I try to connect via our own Oracle-based software (we're in the transportation software and consulting field), I get the following, which I assume is a related error:

"4: Unable to connect: Oracle error occurred, but error message could not be retrieved from Oracle"

Does this help any?

Thanks!
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 20041631
If you can connect via SQL*Plus, then you have at least one "Oracle_home" (a version of the Oracle client software)  installed.  Do you have just that one version of the Oracle software installed, or do you maybe have multiple Oracle_homes, with the networking configured correctly in the one that SQL*Plus uses, but not in the one that ODBC uses?

Which ODBC client are you trying to use (Microsoft's, Oracle's, some other one, etc.)?
0
 
LVL 18

Expert Comment

by:sventhan
ID: 20041824
0
 

Author Comment

by:multimodal
ID: 20041851
markgeer:

We have Oracle Client version 10.1 installed, and are using the Microsoft ODBC driver for Oracle. I was told by the developers here that we need to use the MS driver.

Thanks.

0
 

Author Comment

by:multimodal
ID: 20041937
We only have one version of Oracle client installed (10.1), and are trying to connect through Microsoft's ODBC driver for Oracle.

We did have a previous version of Oracle installed (10.2), but uninstalled it and installed 10.1 instead. 10.2 was mistakenly installed by the user of this laptop. (Everyone else here has 10.1.) Is it possible that the MS ODBC driver or configuration is somehow corrupted? Can it be reinstalled? Sorry, I don't know very much about this.

Thanks.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 20041963
We have used both Oracle10.1 ODBC driver and the Microsoft ODBC driver for Oracle with our Oracle10.1 database.  I am not an expert with either one, and don't need either one for my work (as a DBA, and with Oracle client tools) but I have helped to install and configure both of these for others in our organization who want to use Oracle data with non-Oracle apps.  Have you tried the Oracle ODBC driver?  Do you know why the developers "need" the MS driver?
0
 
LVL 18

Expert Comment

by:sventhan
ID: 20042104
do you have the db home in the PATH. Just try to move it to first in the path and see if that would help.
0
 

Author Comment

by:multimodal
ID: 20042208
Yes, it is in the path, and it is first.
0
 
LVL 18

Accepted Solution

by:
sventhan earned 2000 total points
ID: 20042303
Bring the DLL's up to date. In the registry:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient10.dll"
"OracleSqlLib"="orasql10.dll"
"OracleOciLib"="oci.dll"
0
 

Author Comment

by:multimodal
ID: 20043072
I checked the registry, and could not find "[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]". Could this be part of the problem?

Thanks

0
 
LVL 4

Expert Comment

by:linuxo
ID: 20046586
Hi,

your problem is in your ODBC i think,  try to download Oracle driver

this will solve this issue

http://www.oracle.com/technology/software/tech/windows/odbc/index.html


thanks
0
 

Author Comment

by:multimodal
ID: 20049272
Just for the heck of it (I figured it was worth a shot, anyway), I exported the registry that "sventhan" suggested I update. I then made the modifications as suggested, and TADA!!! I can now connect using the MS ODBS driver.  Thanks sventhan!
0
 
LVL 18

Expert Comment

by:sventhan
ID: 20049325
You're welcome.
Happy to know that its working.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, we’ll look at how to deploy ProxySQL.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question