DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Heterogeneous Service (HS)Connectivity error

Hybrid 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 04:15 AM.

  2. #2
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    hi,

    LOCAL SYSTEM account does not have proper access rigths to execute msaccess.exe.
    Change the TNS Listener Service Startup properties to use a local user instead of
    'System'.

    Go to Start>>Settings>>Control Panel
    Double click Services Icon
    Scroll down until you see the OracleTNSListener
    Highlight this service and click "Stop" button to stop the service
    Select the "Startup" button on the right
    Then, in the 'Log On As' section, select "This Account"
    and provide the user/password that has administrative rights.
    Start the listener.
    ---------------

  3. #3
    Join Date
    Feb 2007
    Posts
    212
    Thanks dear...but I'm sure i got an administrative rights because I am using
    the local administrator account. Would that be a problem too? Do I need to
    provide the administrator user to the "This account" thing?


    Thanks

  4. #4
    Join Date
    Feb 2007
    Posts
    212
    Thanks dear....you are brilliant ....it worked now...
    By the way....can I do it also for dbase IV .dbf or visual foxpro?

  5. #5
    Join Date
    Feb 2007
    Posts
    212
    Hi Hunnies,

    I successfully made connection to ACCESS DB and FOXPRO DB using
    HS Connectivity.

    But when I tried making HS to SQLSERVER DB using the same procedure
    i got this error:

    select count(*) from EMP@SQLSVR_db
    *
    ERROR at line 1:
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    [Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server Driver][SQL
    Server]Cannot open database requested in login 'master'. Login fails. (SQL
    State: 37000; SQL Code: 4060)
    ORA-02063: preceding 2 lines from SQLSVR_DB


    I created EMP table on MASTER DB on PROD_SERVER.
    Why does it fails login on Master my default config DB when I did successful
    connect during the config set-up.

    My sql login userid is SA and with no password.


    Thanks a lot.
    Last edited by jennifer2007; 03-14-2007 at 06:47 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