Accessing Sqlserver from Oracle Database

Scenario:I want to access sql server 2000 database from oracle server 11g.

Following are the details about the servers.

Sql Server
=========
OS= Windows 2003 Server
Sqlserver Version= 2000
IP=192.168.1.100
default port=1433
DB name=hds

Oracle Server
============
OS= AIX 6.1
DB Version=11.1.0.7

Download Oracle Gateway software from OTN
http://download.oracle.com/otn/aix/oracle11g/aix.ppc64_11gR1_gateways.zip

1. Install oracle gateway under the ORACLE_HOME
2. Configure the files under $ORACLE_HOME/dg4msql/admin
copy the default initdg4msql.ora to your init{SID}.ora
cp initdg4msql.ora inithds.ora    (in my case my sqlserver db name is hds)

more inithds.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=192.168.1.100:1433//hds
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

3. Similarly after configuring the gateway file, configure the tnsnames.ora and listener.ora files as:
cd $ORACLE_HOME/network/admin
more listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/11.1.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC=
(SID_NAME=hds)
(ORACLE_HOME=/u01/app/oracle/11.1.0/db_1)
(PROGRAM=dg4msql)
)
)

more tnsnames.ora
CARS2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = EDWDEV)(PORT = 1521))
)
(CONNECT_DATA =
(SID = hds))
(HS=OK)
)
)

4. After restarting the listener check the tnsping status. It should return ok result.
5. Then connect with sqlplus and create a dblink as:
sql>CREATE PUBLIC DATABASE LINK CARS_LNK CONNECT TO “edw” IDENTIFIED BY “edw” USING 'CARS';
Database link created.
sql>
6. After that try to select any data using db link it prompt me errors:
SQL> SELECT * FROM DUAL@cars_lnk;
D
-
X

1 row selected.
SQL>

Please note that in Oracle 11g The User/Passwords are case sensitive so make sure to enclose both in double quots.
If you have multiple sqlserver databases then you have to perform same steps and create seperate init.ora files and place the entries in tnsnames.ora and listener.ora files.
Reference:

http://download-uk.oracle.com/docs/cd/B28359_01/gateways.111/b31043/sqlserver.htm
metalink note : 437374.1

Cheers!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!