This discussion is archived
8 Replies Latest reply: Jan 3, 2013 12:41 AM by 982655 RSS

Cannot connect to Oracle through ODBC.

832601 Newbie
Currently Being Moderated
Please help, I don't know what else to try.

I'm writing automated test scripts and frequently need to access the database to verify data values. These scripts run under multiple environments and have to validate several different flavors of database products. I use ODBC to establish the database connection and this works well under everything except the following environment:

Database Host:
Windows Server 2008, Standard Edition
Oracle 10g

Client:
Windows Server 2008 Standard (64-bit)
Oracle 11g client software installed.

Following recommendations here on the Oracle site, I used the OUI to remove the Oracle ODBC driver that is installed with the client software and installed RDBODBC3320_x64.msi.

I was unable to connect to the database at all using the Oracle 10g client software under this environment, but it is my understanding that Oracle 11g should work. I don't have much choice. The Oracle 10g client refused to install in this environment.

I am able to create a DSN for the database using the ODBC Data Source Administrator. I specify the configuration correctly, but when I try to test the connection, it returns the error:

FAILED: Service Connection test
rc=-1
SQLState=08S01
[Oracle][ODBC][Rdb] Connect Ret -1 Err# 10061
WSAECONNREFUSED Connection refused

I interpret this to mean that I'm reaching the server and database, but it isn't recognizing the connection as being from a legitimate account. Am I wrong?

From this client machine, I am able to connect to and query the database using SQLPlus, so it isn't that the database is unavailable from the test machine.

On the host server, I am able to create an ODBC configuration that tests successfully. That is, of course, the 10g version of the ODBC driver.

I hope someone can supply an answer, this is a high visibility item that is making me look foolish.

Thanks,

Dave P.
  • 1. Re: Cannot connect to Oracle through ODBC.
    gdarling - oracle Expert
    Currently Being Moderated
    H Davei,

    Are you trying to connect to "regular" Oracle database, or an RDB database (they're different things). If you're trying to connect to "regular" Oracle, then you don't want to install the RDB driver. I'm not sure what "recommendations here on the Oracle site" would have suggested that, unless perhaps they were referring to connecting to an RDB database.

    So, at this point, if you have 11g client that connects, but don't have the 11g odbc driver, you probably want to just re-install the 11g odbc driver back into that home via rerunning setup.exe that you installed from in the first place, and selecting 'Custom' install option (make sure you point to your existing home, or it will create a new one).

    Hope it helps,
    Greg
  • 2. Re: Cannot connect to Oracle through ODBC.
    832601 Newbie
    Currently Being Moderated
    Thanks for your reply. What is the difference between a "regular" Oracle and an RDB and how could I tell which one I'm using? I don't know Oracle from a hole in my head, so finding information is very difficult because I don't even know the correct questions to ask. I do know that I've tried installing the 64 bit and 32 bit clients on this 64bit VM. No success with the 64 bit flavor of the software, partial success with 32 bit. I first tried installing the instantClient but then I didn't get the ODBC driver. I get very frustrated with the product because it seems like something as irrelevant as the color of your desktop requires a custom configuration.

    Ok, done ranting now. Sorry, had to blow off steam.
  • 3. Re: Cannot connect to Oracle through ODBC.
    gdarling - oracle Expert
    Currently Being Moderated
    Hi Dave,

    It's difficult to make decent recommendations without knowing for sure even what kind of database you're connecting to, or what issues you're having exactly. "No success with the 64 bit flavor" doesn't provide much info to go on.

    Here's the bullet points that will hopefully get you working, assuming you're connecting to "regular" oracle and that this whole RDB thing was a wild goose chase. If you're actually connecting to an RDB database, skip all the steps below and go post in the RDB forum to get their advice. If you're not sure what the difference is, or what you're connecting to, ask someone who knows.

    *1)* Are you ultimately trying to connect with a 32 bit app or a 64 bit app, from the 64 bit 2008 box?
    - What kind of "scripts" are you writing? VBSscript you're executing from a DOS prompt? If so, how are you invoking it exactly? Just by typing filename.vbs?
    - Are the scripts running with 32bit cscript.exe or 64 bit cscript.exe?

    The bottom line is that 32 bit apps need 32 bit client software, and 64 bit apps need 64 bit client software, and you need to have the right stuff installed. The first thing you need to figure out is whether you're running 32 bit app or 64 bit process. Usually you can open Task Manager and look for the process name in the Process tab; if there's a *32 next to it, it's 32 bit. Otherwise it's 64 bit.

    *2)* Once you determine what bitness the software you're running is, you then need to download and install that bitness of Oracle client software. You can get 11.2.0.1 here:

    32 bit 11.2 client:
    http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html
    64 bit 11.2 client:
    http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

    *3)* After downloading the client.zip and extracting it, run setup.exe, take the defaults, chose Custom install type, select ODBC driver and SQLPlus from the list, and the installer will automatically install the Oracle software as well.

    Note: You could use Instant Client for this as well; but you'll have to download and install SQLPlus and the ODBC driver in addition to the instant client itself, and using the OUI and the Full client installation is "easier" as it doesn't require manual installation like Instant client does, so less error prone.

    *4)* Configure tnsnames.ora for that install so the client knows where you want to connect to, test it using sqlplus from that install location to make sure you have it right.

    *5)* If you want to configure a 32 bit dsn, you need to use the 32 bit administrator ( c:\windows\syswow64\odbcad32.exe).
    64 bit dsn's you configure via c:\windows\system32\odbcad32.exe (or via control panel > odbc administrator).

    *6)* Run your app, hopefully you're dialed.

    *7)* Profit.


    Hope it helps,
    Greg
  • 4. Re: Cannot connect to Oracle through ODBC.
    947965 Newbie
    Currently Being Moderated
    i faced the same problem but with win 7 64-bit host with oracle 10g client
    - FYI windows 7 has two diffrent ODBC :
    a 64-bit version located in C:\Windows\System32\odbcad32.exe
    a 32-bit version located in C:\Windows\SysWOW64\odbcad32.exe

    if you are using oracle 104 Database server . and need to connect to it from a 64-bit client you have to do the following
    1- open ODBC 32-bit version and select system DSN : Microsoft ODBC for Oracle
    2- run that command regsvr32 c:\Windows\SysWOW64\odbcconf.dll which make windows read from both ODBC versions
    3- log with an administrator account on windows and run ypur applcation it will work fine
  • 5. Re: Cannot connect to Oracle through ODBC.
    960197 Newbie
    Currently Being Moderated
    Awesome.
  • 6. Re: Cannot connect to Oracle through ODBC.
    239481 Newbie
    Currently Being Moderated
    I am running windows 7. I installed Oracle ODBC 64 bit by running the setup. It creates the driver in ODBC Admin. Then i create a DSN and Test Connection successfully.

    However when I use the DSN in Microsoft Access I get the message "The specified DSN contains an architecture mismatch between the Driver and application.

    I figured that my Access is 32 bit since it is installed in c:\Program Files (x86). So I download the 32bit driver. There is an odbc_install file but running that, I get a brief command window and it closes. The driver is not installed.

    Anyone can help?
  • 7. Re: Cannot connect to Oracle through ODBC.
    Tridus Journeyer
    Currently Being Moderated
    Download the full installer version instead of the xcopy one, and just run setup.exe. It'll install everything you need.
  • 8. Re: Cannot connect to Oracle through ODBC.
    982655 Newbie
    Currently Being Moderated
    Establishing the connection with ODBC in oracle for crystal reports 2011
    Steps:

    1.     32-bit version located in C:\Windows\SysWOW64\odbcad32.exe
    2.     Select "System SDN Tab" in ODBC Data source Administrator
    3.     Click Add button select Microsoft ODBC for Oracle
    4. Select "Microsoft ODBC for Oracle" and click Finish button
              
    Server : This Entry is coming from tnsname.ora file
         
    MAIN_PUNE =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 163.183.116.231)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = demo)
    (SERVER = DEDICATED)
    )
    )

    5. Now open Crystals report [Create New Connection]


    6. Select ODBC (RDO)


    Thanks and Regards,
    Parixit Pandey

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points