Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    1

    Wink Unanswered: Oracle to SQL Server connection

    Want to connect from an Oracle database (8i) on solaris to a SQL server
    database. Clients can't/won't pay for the Oracle gateway product, can anyone recommend a 3rd party client ODBC driver (will install DBI:ODBC on top of this).

    Thanks in anticipation

  2. #2
    Join Date
    Jul 2003
    Posts
    2

    connecting Oracle to SQL Server

    Please follow the link for help:
    http://www.oracle-base.com/Articles/...ectivity9i.asp

    Heterogeneous Services - Generic Connectivity In Oracle9i
    Oracle9i, like previous versions, supports heterogeneous services to allow data in non-Oracle database to be queried using SQL. This support has been in the form of transparent gateways, which are vendor specific, or generic connectivity which uses ODBC or OLEDB to make the connections. The functionality supported by generic connectivity is typically more limited than that possible when using vendor specific gateways, but it is quick and simple to configure. The steps listed below can be used to connect Oracle to any ODBC compliant database (MS Access, SQL Server etc.) on the local server:
    Make sure a valid username and password are present for the non-Oracle database (MyUser/MyPassword).
    Create an ODBC DataSource for the non-Oracle database (Access1).
    Create a file called: ORACLE_HOME\hs\admin\initACCESS1.ora which contains:

    HS_FDS_CONNECT_INFO = Access1 #ODBC DSN
    HS_FDS_TRACE_LEVEL = OFF
    Add the following entry to the tnsnames.ora file:

    ACCESS1.WORLD =
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521) )
    (CONNECT_DATA=(SID=ACCESS1))
    (HS=OK)
    )
    Add the following entry into the listener.ora file:

    (SID_DESC=
    (SID_NAME=ACCESS1)
    (ORACLE_HOME=D:\Oracle\Ora9011)
    (PROGRAM=hsodbc)
    )
    Reload the listener: lsnrctl reload
    Create a database link using:

    CREATE DATABASE LINK access1.world CONNECT TO "MyUser" IDENTIFIED BY "MyPassword" USING 'ACCESS1.WORLD';
    Query the table using:

    SELECT *
    FROM mytable@ACCESS1.WORLD;
    With MS Access the first query is slow due to the Access application being loaded. As expected, subsequent queries do not display a similar lag.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •