Heterogeneous Service (HS)Connectivity error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Heterogeneous Service (HS)Connectivity error

Threaded View

  1. #1
    Join Date
    Feb 2007
    Posts
    212

    Heterogeneous Service (HS)Connectivity error

    Hi Friends,

    I follow this procedure correctlty...but I still can not successfully
    connect to my access db. Please help.

    =========================== Procedure to configure HS ==============

    Connecting Oracle with MS-Access
    Submitted by admin on Sun, 2005-05-15 06:00. RDBMS Server
    This article shows how Oracle's Heterogeneous Services can be configured to allow a database to connect to a Microsoft Access database using standard databases links. The method described can be used to connect to MS-Access from about any platform - Unix/ Linux or Windows.

    MS-Access 2003 and Oracle 10g Release 1 are used to illustrate the concepts. However, this procedure should work with Oracle 8i, 9i and 10g databases, as well as various versions of MS-Access.

    Step 1: Prepare the MS-Access environment

    If you do not have a MS-Access environment, start by installing the required software and create a test table.

    Step 2: Define ODBC connectivity

    Use the ODBC Administrator Utility to define a local System DSN that can be used to connect to the Access database (same machine). Ensure that the correct *.MDB database file is selected.

    Step 3: Prepare the Oracle Environment

    Install the Oracle Database Server software on the same machine where MS-Access is installed.

    NOTE: It is not sufficient to only install Client Software, as we require an Oracle Net Listener and the Heterogeneous Services (ORACLE_HOME\hs directory) software to be installed as well.

    Step 4: Configure and Start the Oracle Listener

    Configure the Oracle Listener on the Windows machine. Here is a sample LISTENER.ORA entry that can be used. Change the HOST, PORT and ORACLE_HOME entries to match your setup. You may also use a different SID_NAME if required.


    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = my_windows_machine_name)(PORT = 1521))
    )
    )
    )
    SID_LIST_LISTENER=
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = hsodbc)
    (ORACLE_HOME = c:\Oracle\Ora101)
    (PROGRAM = hsodbc)
    )
    )
    Stop and start the listener service or from the command line:
    C:\> lsnrctl stop
    C:\> lsnrctl start

    Step 5: Configure Oracle HS:

    Edit the ORACLE_HOME\hs\admin\inithsodbc.ora file and add your ODBC System DSN Name (ODBC1 in our case as defined in step 3).


    HS_FDS_CONNECT_INFO = odbc1
    HS_FDS_TRACE_LEVEL = off
    Note: If you used a custom SID_NAME in step 4, name the file accordingly - INIT.ORA.

    Step 6: Configure Oracle connectivity to Windows Machine

    From now on we are going to work on the Oracle Server (Unix or whatever you run) add the following TNSNAMES.ORA entry:


    access_db.world =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.31) (PORT = 1521)
    )
    (CONNECT_DATA =
    (SID = hsodbc)
    )
    (HS=OK)
    )
    Ensure you can tnsping the new entry before continuing.

    Step 7: Create a database link

    Create a database link using the entry defined in step 6.


    SQL> CREATE DATABASE LINK access_db USING 'access_db.world';

    Database link created.
    The tables in the access database can now be queried from the Oracle environment.


    SQL> SELECT * FROM my_access_tab@access_db;

    ID Field1 Field2
    ---------- -------------------- --------------------
    1 row1col1 row1col2
    2 row2col1 row2col2
    3 row3col1 row3col2

    SQL> CREATE TABLE my_oracle_tab AS SELECT * FROM my_access_tab@access_db;

    Table created.

    ========================== end of procedure ==================

    But when I create my database link, I got this error.

    SQL> CONNECT SYSTEM/oracle
    Connected.
    SQL> CREATE DATABASE LINK access_db USING 'access_db.world';

    Database link created.


    SQL> select * from emp@access_db;
    select * from emp@access_db
    *
    ERROR at line 1:
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    [Generic Connectivity Using ODBC][Microsoft][ODBC Driver Manager] Data source
    name not found and no default driver specified (SQL State: IM002; SQL Code: 0)
    ORA-02063: preceding 2 lines from ACCESS_DB

    I have configured my ODBC for Ms Access driver also.
    Where could I have gone wrong?

    ========== My listener.ora=======




    =========== My tnsnames.ora

    # tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
    # Generated by Oracle configuration tools.

    ORCL =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.56)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
    )
    )

    access_db.world =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.56)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = hsodbc)
    )
    (HS=OK)
    )

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )

    ============== My listener.ora

    # listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
    # Generated by Oracle configuration tools.

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (SID_NAME = hsodbc)
    (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
    (PROGRAM = hsodbc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = orcl)
    (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
    (SID_NAME = orcl)
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.56)(PORT = 1521))
    )
    )
    Last edited by jennifer2007; 02-21-2007 at 03:15 AM.

Posting Permissions

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



Click Here to Expand Forum to Full Width