Hello Fareed,

There are two ways to connect, depends on the direction:
1. Define a connection in SQL Server to Oracle is called Linked Server.
2. Define a connection in Oracle to SQL Server is called Heterogeneous
Services.

I. Linked Server
----------------------

The following were the steps we took to create a Linked Server from SQL
Server 2000 to Oracle 8i.
1.Install the Oracle client on the SQL Server host machine.
2.Do this registry edit (for Microsoft Windows 2000):
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTCMTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
3.Create an ODBC Data Source to Oracle:
System DSN -> Add
Oracle ODBC driver
Data Source Name = (Oracle Connect String - ex. ORACLE_PROD)
Description = (Oracle Connect String - ex. ORACLE_PROD)
Service Name = (Oracle Connect String - ex. ORACLE_PROD)
UserID = sqldba (Oracle User ID)
4.Reboot
5.Set up the Linked Server:
Provider Name = Microsoft OLE DB Provider for Oracle
Product Name = (leave blank)
Data Source = (DSN Name from step 3)
Provider string = MSDAORA
6.Test:
Select * from ORACLE_PROD..(Database Schema).(Table Name)

II. Heterogeneous Services
--------------------------------------

The following were the steps we took to create Heterogeneous Service
connection from Oracle 8i to SQL Server 2000.
1.Install Generic Connectivity using ODBC. This product is part of the
server installation, because a listener is neaded. A directory called HS is
created under $oracle_home
2.The Generic Connectivity utility needs data dictionary tables in the
Oracle database. To check their existence, run a query on i.e.
SYS.HS_FDS_CLASS. If it fails, run the caths.sql script located in
\ORACLE_HOME\RDBMS\ADMIN as user sys or internal.
3.If it not already installed, install the third party ODBC driver from
Microsoft or Merant.
With ODBC Admin Utility configure a SYSTEM DATASOURCE. i.e. specify as Data
Source Name and select as SQL Server. Use SQL Server autentication (instead
of Windows NT autentication) and type a legal username and password to the
SQL Server database. Be sure to use TCP/IP if the Named pipe port is not
available. See note for default database, below.
4.Now configure tnsnames.ora. This file is in ORACLE_HOME\NETWORK\ADMIN.
Add the following lines to the file:
hsmsql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=tcp)(host=b1es1122)(port=1526)) --Oracle
DB Host
)
(CONNECT_DATA =
(SID = SqlSrv) =====> The Same that will be
Defined in the Listner.Ora
)
(HS=OK)
)

5.Configured listener.ora. This file is in ORACLE_HOME\NETWORK\ADMIN.
Add the following line to the SID_List of the listener.ora and restar (or
reload) the listener afterwards.
LISTENER =
) DESCRIPTION_LIST =
) DESCRIPTION =
) ADDRESS_LIST =
) ADDRESS = (PROTOCOL = TCP)(HOST = b1es1122)(PORT =
1526((
(
(
(

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = SQLSRV) ====== > Match the SID name in
the TnsNames .
(ORACLE_HOME = c:\oracle\ora81)
(PROGRAM = hsodbc) ====== > the Orcale Agent
Executable
)
)

6.Adjust the configuration file of the gateway. It is located in
ORCALE_HOME\HS\ADMIN. The name depends on the SID you use for the
Heterogeneous Service. In our sample the listener SID is hsodbc and so 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 = mstest |<- ODBC Data Source Name configured in
step 4
HS_FDS_TRACE_LEVEL = 0 |<- trace levels are from 0 to 4 (4 is
maximum)
7.Testing the connectivity between Oracle database and SQL Server database:
create a database link
create database link hsodbc connect to [user] identified by [password] using
'hsnsql'; ..=> the Service Name in the Tnsname.



------------------------------------------------------------------------------------------------------------------------------

The information contained in this e-mail message is the proprietary of Barak
I.T.C (1995)The International Telecommunications Services Corp. Ltd. and is
privileged and confidential. The information is intended only for the use of the
individual or entity named above. If the reader of this message is not the
intended recipient, you are hereby notified that any dissemination, distribution
or copying of this communication is strictly prohibited. If you have received
this communication in error, please notify us immediately by telephone, or by
e-mail and delete the message from your computer. Thank you!

------------------------------------------------------------------------------------------------------------------------------

Oracle LazyDBA home page