This guide bellow describe how to setup a database link between a Microsoft SQL Server and the Oracle Database (for Windows NT/2000) using Oracle Heterogeneous Services. Let me if you need to guide from OS AIX.

1) Create the DSN system source in Microsoft ODBC Administrator (e.g. "sqlmis").

2) Adjust the configuration file of the gateway, located in ORACLE_HOME/HS/ADMIN. The name depends on the SID you use for the Heterogeneous Service. We has used the same name sugested by ORACLE guide (hsodbc). In this case the configuration file is <inithsodbc.ora>.

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = sqlmis |<- ODBC Data Source Name configured in step 1
HS_FDS_TRACE_LEVEL = 0 |<- trace levels are from 0 to 4
HS_OPEN_CURSORS = 300 |<- this parameter has solved the ORACLE error.

3) Configure tnsnames.ora in ORACLE_HOME\NETWORK\ADMIN. Add the following lines to the file:

hsodbc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <your HOSTNAME> ) (PORT = 1521))
)
(CONNECT_DATA = (SID = hsodbc))
(HS=OK)
)

4) Now configure listener.ora in ORACLE_HOME\NETWORK\ADMIN. Add the following lines to the file in SID_List and restart the listener afterwards:

(SID_DESC =
(SID_NAME = hsodbc)
(ORACLE_HOME = D:\oracle\ora81) |<- your ORACLE_HOME
(PROGRAM = hsodbc )
)

5) Create a database link:

In this sample : <create database link hsodbc connect to "user" identified by "password" using 'hsodbc'>

Please note that "user" and "password" are the user granted in SQL Server.

6) Follow the steps below:

> Stop the database server;
> Edit the INIT.ORA file;
> Set the global_names = false;
> Restart the database server.

Regards.

Délson Castro.
--------------------------------------------------------------
Délson Castro Gonçalves
Coordenador de Informática
Royal & SunAlliance Seguros
Tel : 55 21 2223-7711
E-mail: delson.castro@br.royalsun.com
http://www.royalsun.com.br
--------------------------------------------------------------