Using Generic Connectivity To Access Microsoft SQL Server

March 27th, 2004 by Mark Rittman

"I am trying to link 2 databases through Business Objects to enable Bus
Obs users to run their own reports (querying data from both databases
’simultaneously’) and have reached a stumbling block because (as I’m sure
you’re aware) database links between Oracle and another database (SQL Server
in this instance) is an ‘elaborate’ process which, I have not yet managed to
figure out. I suppose that my question is; have you done something like this
before (linking different databases)? Or do you know how this could be done."

The simple way to connect an Oracle database to an ODBC-compliant data source
(for example, Microsoft SQL Server) is to use the database Generic Connectivity
feature. For details of how to use Generic Connectivity for Oracle 9i, check out
the

Generic Connectivity section in the

Heterogeneous Connectivity Administrator’s Guide.
 

Posted in Oracle Database |

Comments

  1. Kalyani Says:

    May 4th, 2004 at 7:26 pm

    I am trying to connect from Oracle to Microsoft Access and I followed the documentation in Hetrogeneous Connectivity Adms guide but was not sucessful. I am attaching my code and I would be greatful if someone could tell me where I went wrong :
    1)Created an ODBC connection (System DSN) for Microsoft Access called ‘MSACCESS’. And associated accdb1.mdb to this ODBC connection.
    2)Created a table called orders in accdb1.mdb.
    3)Added the foll lines in tnsnames.ora
    accdb1 =
    (DESCRIPTION=
    (ADDRESS=
    (PROTOCOL=tcp)
    (HOST=kdandapani.170systems.com)
    (PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=accdb1)
    (HS=OK)
    )
    4)added the following lines in listner.ora :
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME=accdb1)
    (ORACLE_HOME=c:\orasrv)
    (PROGRAM=accdb1)
    )
    )
    5) Copied inithsodbc.ora to iniths_accdb1.ora in the ORACLE_HOME/hs/admin directory. Added the following line there :
    HS_FDS_CONNECT_INFO = MSACCESS
    6)Created the foll dblink :
    create database link access1
    using ‘accdb1′;
    7)tried to access table ORDERS in the MSACCESS database with the foll SQL -
    select * from orders@access1;
    Resulted in the foll error message
    ERROR at line 1:
    ORA-12154: TNS:could not resolve service name

  2. Scott Powell Says:

    November 12th, 2007 at 3:53 pm

    Mark, I was curious to see if you’ve tested generic connectivity under 11g (specifically Windows). I’m trying to get ODBC connectivity to work to a Netezza database. I have “basic” SQL selects working fine, but any “metadata” like SQL (for example, select * from all_tables@dblink) fail, saying something like “Relations ALL_TABLES does not exist)…

    My understanding is that Oracle is supposed to know how to communicate to the ODBC server, and properly retrieve metadata-like queries. I’m trying to get this working so that I can use OWB against these sources. Have you had any luck with this in 11g?

    Thx,
    Scott

  3. Peter Scott Says:

    November 12th, 2007 at 7:36 pm

    Scott,

    ALL_TABLES is an Oracle View that exposes the tables in the data dictionary. It is quite possible that Netezza use their own structures (with different names!) Take a look in the Netezza manuals for guidance on this.

    Also check the version of the Netezza ODBC driver you are using - recent ones are much more feature rich than old ones. Also JDBC drivers exist - this may be an option for you

    But what you are trying to do sounds unusual so I doubt that you will find many who have “been there, done it”