Solved

ODBC ERROR Using MS ODBC for Oracle

Posted on 2006-06-15
2
8,211 Views
Last Modified: 2013-12-12
In a web application using ASP and Oracle, after moving to a new Win 2003 server and re-installing the software,  we had the application working fine, and then the next day we started getting this error:

 "ERROR      2001      Microsoft OLE DB Provider for ODBC Drivers [Microsoft][ODBC driver for Oracle][Oracle] (-2147467259)".  

We can ping the database with no problem.  I set up an Access database and linked to the one of the Oracle tables readily. However, if I close Access and reopen it, I get the "ODBC Connection to [DSN name] failed."  error.  I created a new system ODBC connection using the Oracle ODBC driver and then used that to link to one of the Oracle tables in Access. Worked fine again, until I closed Access and reopened it, then I get the same error as with the Microsoft ODBC DSN.

I have added the IUSER to all the many places (registry, all the relevant directories) to no avail.  I have searched everywhere on this problem and done everything that seems relevant to no avail.  Downloaded new MDAC file from Microsoft and installed it, no difference.

Does anyone have any ideas?  About to pull all my hair out!!

Susan
0
Comment
Question by:beechers
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 

Author Comment

by:beechers
ID: 16943126
I am giving myself the 500 points.  ;-)  I found the solution via searching on Google (despite hours of searching previously).  
To give credit where credit is due:
This is from http://www.orafaq.com/forum/t/37835/2/

SQLSetConnectAttr error : Microsoft ODBC for Oracle, and Oracle 9i client on XP [message #99667] Tue, 12 February 2002 16:47  
Joe Springer
Had a major problem, but solved it, so am posting here so that someone may
benefit.
Attempting to create an ODBC DSN using Microsoft ODBC for Oracle
(MSORCL32.DLL of 8/23/2001) on Windows XP (which comes with MDAC 2.7), get
the following error:
"Driver's SQLSetConnectAttr failed"

Problem is that the ODBC settings expect Oracle 8.0 to be used by default. So
change the registry to look for Oracle 9 DLLs and DSN creation succeeds.
Need to change 2 registry entries, see HKLMSoftwareMicrosoftMSDTCMTxOCI
and change OracleXaLib from oraclient8.dll to oraclient9.dll and change
OracleSqlLib from orasql8.dll to orasql9.dll (note: if OracleOciLib is
oci.dll do not change it).
(As a reference see outdated Microsoft article Q264012).  

My registry settings were:
HKLM\MSDTC\MTxOCI: OracleSQLLib = SQLLib80.dll
                 : OracleXALIB = Xa80.dll
                 : OracleOciLib = oci.dll

I was initially hesitant because our settings were so different from what he indicated they would be.

However, found the following:

http://forums.oracle.com/forums/message.jspa?messageID=965129

 MDAC 2.8 and Oracle 10g
Posted: May 11, 2005 1:41 PM      Posted by cnikkel
MDAC 2.8 (Microsoft ODBC for Oracle) will connect to Oracle 9i but not 10g,
any ideas? Thanks.

Mark A. Williams  
  Re: MDAC 2.8 and Oracle 10g
Posted: May 12, 2005 6:57 AM    in response to: cnikkel      
 I see you have also posted to the ODBC forum so you might get better help
from there, but, I was able to connect using the MS ODBC driver from MDAC 2.8
to 10g with no problems at all. What error did you receive?
- Mark

Reply  from cnikkel:
I am using winsql with the msorcl32.dll 2.575.1022.00 driver and am getting
the following error, which is not very helpful.
ODBC Error SQL Error State:NA000, Native Code: 0, ODBC Error: [Microsoft][ODBC driver
for Oracle][Oracle]
OK

Mark A. Williams  
  Re: MDAC 2.8 and Oracle 10g
Posted: May 12, 2005 7:27 AM    in response to: cnikkel      
I'm not familiar with winsql, but what I did was create a data source, create
an Access database, then attach a table in a 10g database using that data
source. My version of the driver is 2.575.1117.00 for what it's worth. I
agree that the error message is, umm, slightly less than helpful.
- Mark

cnikkel  
Re: MDAC 2.8 and Oracle 10g
Posted: May 12, 2005 8:03 AM    
OK I am using your version of the driver, same error.
I have now tried as you did in MS Access, and am getting a better error message.
Microsoft Office Access
ODBC--call failed.
[Microsoft][ODBC driver for Oracle][Oracle](#0)[Microsoft][ODBC Driver
Manager] Driver's SQLSetConnectAttr failed IM006 0 [Microsoft][ODBC Driver
Manager] Driver's SQLSetConnectAttr failed (#0)

cnikkel  
Re: MDAC 2.8 and Oracle 10g
Posted: May 13, 2005 6:50 AM    
I got it, thanks Mark
http://support.microsoft.com/?id=264012
Update the following registry key with the following information
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient10.dll" "OracleSqlLib"="orasql10.dll"
"OracleOciLib"="oci.dll"
Make sure your Windows environment variable Path includes the Oracle 10g bin
directory:  C:\Oracle\product\10.1.0\Client_1\bin

 So I changed my registy settings as directed immediately above only putting 9 where ever he had 10 as we are on Oracle 9.2.   That fixed my problems.
Susan
 

0
 

Accepted Solution

by:
EE_AutoDeleter earned 0 total points
ID: 17093381
beechers,
Because you have presented a solution to your own problem which may be helpful to future searches, this question is now PAQed and your points have been refunded.

EE_AutoDeleter
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL/SQL Two changes 7 41
Trying connecting to SQL server 2016 from sql developer using Third Party JDBC Drivers, 2 37
plsql job on oracle 18 78
oracle query 3 26
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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.

733 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