Making a Connection from Oracle to SQL Server
Here is how to make a connection from Oracle to SQL Server using Oracles heterogeneous Services ODBC agent.
Many database shops that have more than just Oracle. In fact, SQL Server is gaining popularity and there are many shops that I know of that have this database system within their walls. Oracle has a generic connectivity methodology that allows for the Oracle database server to access non-Oracle database systems through ODBC and SQL*Net services. This article explores in a step-by-step fashion setting up this connection so that we may query from a SQL Server database.
How to Define a Data Source Name (DSN) for SQL Server
The first step is to define a system DSN within the Windows ODBC Data Sources.
a. From the start menu click on Settings -> Control Panel and double click the ODBC icon.
b. Click on the System DSN tab and then click the Add button.
c. Choose the SQL Server driver since this will be a connection to SQL Server. Click Finish to continue with the data source definition.
d. 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.
e. I accepted all the defaults here. Click Next to continue.
f. 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. Click Next to continue.
g. I also left this window alone and clicked Finish.
h. 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.
i. 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.
j. 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.