-
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.
-
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.
---------------
-
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
-
Thanks dear....you are brilliant ....it worked now...
By the way....can I do it also for dbase IV .dbf or visual foxpro?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|