How to connect Oracle Database to Microsoft SQL Server by using Heterogeneous Services

In the name of Allah, the most Beneficial, the most Merciful

In this article i will be describing "How to connect Oracle Database to Microsoft SQL Server by using Heterogeneous Services". Access and other databases can also be connected by this method. First of all we need to create ODBC connection on oracle host machine. Go to "Control Panel" -> "Administrative Tools" -> "Data Sources (ODBC)" following screen will appear.

Figure - 1

Click on Add button.

 

Figure - 2

Select "SQL Server" and click Finish button.

 

Figure - 3

Name the Data Source and provide the Server information as shown above and click Next button.

Figure - 4

Provide login information and click Next button.

 

Figure - 5

Select the default database from list and click Next button.

 

Figure - 6

Optionally check the logging for query and statistics and click Finish button.

Figure - 7

Click the "Test Data Source" button.

 

Figure - 8

Click Ok button. Now create a init<ODBC>.ora file in ORACLE_HOME\hs\admin as follows.

Figure - 9

Add the SID_DESC in SID_LIST_LISTENER parameter in listener.ora file as shown in screen below.

Figure - 10

Now restart listener by "lsnrctl reload" command.

lsnrctl reload

Create a connection sting in tnsnames.ora file as shown in figure below.

Figure - 11

check the string by tnsping command

tnsping attendence

 Now create a database link for the Sql Server database as follows.

CREATE PUBLIC DATABASE LINK sqlsrv CONNECT TO "sa" IDENTIFIED BY "MyPassword" USING 'attendence';

Now query the table in SQL Server from oracle by following query.

SELECT "acol", "bcol"

FROM     atab@SQLSRV;

Insha-Allah this will help and serve the purpose. For any corrections and improvements please suggest.