Database Link with Oracle to MSSQLServer

Here I have posted about how to connect with MSSQLSERVER from Oracle. There is a difference between Oracle 10g and 11g. In 10g parameter HSODBC is used inithsodbc file and In 11g DG4ODBC is used in initdg4odbc file.

Using Generic Connectivity (HSODBC) we can create db link between Oracle and SQL server.


Machine (1)
DB Version : Oracle 10g
Operating System : RHEL 5.4
IP : 192.168.0.31
Host : abcdbt

Machine (2)
Version : SQL Server 2005
Operating System : Windows server 2008 x86
IP : 192.168.0.175
Host : SQLDEV1
User/PW : sa/abc@123! (Connect to database)
Database : SQLTEST (exsisting)
Table : T (“ T “ is the table existing in SQLTEST database with 10 rows)


Prerequisites in Machine (2):
a) Oracle 10g software
b) User account to access SQL Server database (sa/abc@123!)
c) Existing SQL Server Database (SQLTEST)
d) Tables (testing purpose) (T)

Steps:
1) Install Oracle 10.2.0.1 (Only SW,No need of database) (Machine 2)
2) Create a DSN where your windows Oracle 10g SW resides (Machine 2)

Control panel >> Administrative Tools >> Data Source (ODBC) >> System DSN ADD

Define a Data Source Name (DSN) for SQL Server
The first step is to define a system DSN within the Windows ODBC Data Sources.

2a. From the start menu click on Settings -> Control Panel and double click the ODBC icon.
2b. Click on the System DSN tab and then click the Add button.
2c. Choose the SQL Server driver since this will be a connection to SQL Server. Click Finish to continue with the data source definition.
2d. Key in any name you would like to reference this ODBC data source. I have chose MYSQLSERVERDSN for simplistic reasons but it should be descriptive to the database you may be connecting to within SQL Server. You may also describe the data source in any way you wish. This is my local SQL Server that I will be connecting to. Click Next to continue.
2e. I accepted all the defaults here. Click Next to continue.
2f. Typically, this window is populated with the default SQL Server database of "master." Click the check box to change the default database this ODBC connection should connect to and use the drop down list to select. I have chosen to use the sample Northwind database.
2g. I also left this window alone and clicked Finish.
2h. This window then appears for you to look at the settings you have configured for the data source. Click Test Data Source to validate your definition.
2i. This window should appear, in which case you have successfully configured the data source. Click OK to close all windows as you are done with the data source definition.
2j. The end product should be a valid System DSN. You may in the future click on the DSN name and click the Configure button to change the definition if you like. I did this when I wanted to switch between databases. A small warning here on re-configuration of the DSN: you will need to drop and re-create the database link (shown later) to activate the DSN. Click OK to exit the DSN administrator.



3) Create a hsodbc init file in $ORACLE_HOME\hs\admin (Machine2)

Create init file

Ex: initSQLTEST

Copy inithsodbc to initSQLTEST

And edit
initSQLTEST file

HS_FDS_CONNECT_INFO = SQLTEST
HS_FDS_TRACE_LEVEL = OFF

save the file....@

4) Configure Listener.ora (Machine 2)

LISTENER_NEW =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.175)(PORT = 1525))
)
)
)
SID_LIST_LISTENER_NEW =
(SID_LIST =
(SID_DESC =
(SID_NAME = SQLTEST) < Here SQLTEST is DSN NAME >
(ORACLE_HOME = G:\oracle 10g\oracle\product\10.2.0\db_1)
(PROGRAM = hsodbc))
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = G:\oracle 10g\oracle\product\10.2.0\db_1)
(PROGRAM = extproc) )
)

:> lsnrctl start LISTENER_NEW


5) Configure tnsname.ora (Machine 2)

SQLTEST11 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.175)(PORT = 1525))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SQLTEST))
(HS=OK)
)


:> tnsping SQLTEST11

If No errors then conti….


6) Configure a file (Machine 1)

Cd $TNS_ADMIN ($ORACLE_HOME/network/admin)

Create a file

$ vi TEST_abcdbt_ifile.ora

something=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST =192.168.0.175) (PORT=1525))
(CONNECT_DATA=
(SID=SQLTEST))
(HS=OK)
)

$ tnsping something

$ sqlplus system/manager

Your connected to Oracle database (machine 1)

create database link xyz connect to “sa” identified by “abc@123!” using ‘SOMETHING’;

select * from t@xyz;

I have followed below links
http://forums.oracle.com/forums/thread.jspa?threadID=1017250
http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1/Making-a-Connection-from-Oracle-to-SQL-Server.htm
and Oracle Support