This discussion is archived
3 Replies Latest reply: May 21, 2012 3:23 AM by 933437 RSS

Cannot see ODBC driver as a data source

933437 Newbie
Currently Being Moderated
Hi
I've installed both the Win32 and Win64 11.2g clients on our Win2008R2sp1 server.

I can get my connection to test fine from c:\windows\sysWOW64\odbcad32.exe (the 32bit ODBC Admin panel), and from sqlplus and sqldeveloper (all on the "client_2" x32bit product).

I've set ORACLE_HOME to the 32bit product.
D:\>echo %ORACLE_HOME%
D:\oracle\product\11.2.0\client_2

I have Strawberry Perl x32 installed and a x32bit VB Application. Neither will connect.
Perl...
D:\>perl -MDBI -le "my $h = DBI->connect('dbi:ODBC:dbase02',rouser,rouser); $h->disconnect()"DBI connect('dbase02','rouser',...) failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002) at -e line 1

On my App I get
S1000: [oracle]p[ODBC][Ora]ORA-12560: TNS:protocol adapter error.

As I've said, the System DSN is on the 32bit ODBC Panel and is 'test's fine from there.

Oddly enough I have a few identical servers where I'd set up connections that work fine, using the same setup. Could be we've had a patch that messed things up? Any ideas on how to get roud the SQL-IM002 or ORA-12560 error would be greatly appreciated.

UPDATE: on servers where we have a working System DSN, if I simply clone it (open existing, change TNS Name and Identified and Save) the cloned 'new' System DSN works fine. However if I create it from scratch it does not! So something is prohibiting me from creating new System DSN entries that are accepted/read by the ODBC Driver???

Many Thanks
Kieran

Edited by: user10688427 on 26-Apr-2012 06:18
  • 1. Re: Cannot see ODBC driver as a data source
    933437 Newbie
    Currently Being Moderated
    Can anyone help me out here?

    I've written a small test script to demonstrate the issue.
    You can clearly see that the DSN is available but I cannot connect, even using a Win32 perl module.

    D:\>perl ODBCCheck.pl
    ODBC Drivers
    ADO, DBM, ExampleP, File, Gofer, ODBC, Pg, Proxy, SQLite, Sponge, mysql
    -------------------------------------------------------------------------
    DBD::ODBCinstalled
    -------------------------------------------------------------------------
    dbi:ODBC:DBASE02
    -------------------------------------------------------------------------
    DBI connect('DBASE02','browser',...) failed: [Microsoft][ODBC Driver Manager] Data source name not
    found and no default driver specified (SQL-IM002) at ODBCCheck.pl line 26
    -------------------------------------------------------------------------
    Can't connect to data source 'dbi:Win32-ODBC:DBASE02' because I can't work out what driver to use (
    it doesn't seem to contain a 'dbi:driver:' prefix and the DBI_DRIVER env var is not set) at ODBCChec
    k.pl line 31

    <code>
    #!perl -w
    use DBI;
    use Win32::ODBC;

    print "ODBC Drivers \n";
    my @drivers = DBI->available_drivers;
    print join(", ", @drivers), "\n";

    print "-------------------------------------------------------------------------\n";

    my $d = join("", @drivers);
    print "DBD::ODBC";
    print "not" if ($d !~ /ODBC/);
    print "installed\n";

    print "-------------------------------------------------------------------------\n";

    my @dsns = DBI->data_sources('ODBC');
    foreach my $d (@dsns)
    {
    print "$d\n";
    }

    print "-------------------------------------------------------------------------\n";

    my $dbh = DBI->connect('dbi:ODBC:DBASE02', 'browser', 'browser');
    $dbh->disconnect() if ($dbh);

    print "-------------------------------------------------------------------------\n";

    my $dbh32 = DBI->connect('dbi:Win32-ODBC:DBASE02', 'browser', 'browser');
    $dbh32->disconnect() if ($dbh32);
  • 2. Re: Cannot see ODBC driver as a data source
    933437 Newbie
    Currently Being Moderated
    The standalone uninstaller will not completely remove registry references to System DSN values, imo.
    So even after a new install of the 11gr2 client you might find your connection will not work via ODBC.

    Rip every reference to your DSN out of the registry first and delete it from you WOW64 32bit ODBC Admin Client.

    Then "Add" it back in, now it should work.

    Collegue of mine found this on a Crystal Reports forum.

    Worked for me.
  • 3. Re: Cannot see ODBC driver as a data source
    933437 Newbie
    Currently Being Moderated
    Another 'trick' sems to be to configure the bad System DSN entry (DB-A), change the Name and TNS Ref to another database (say DB-B) and save.
    Test "DB-B", it works.
    Remove "DB-A"
    Repeat above , configure "DB-B", set the name to "DB-A" and now "DB-A" works too.

    Creating a System DSN using "Configure", seems to work better than "Add" !!