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.
