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.

1. Define a Data Source Name (DSN) for SQL Server

The first step is to define a system DSN within the Windows ODBC Data Sources.

1. From the start menu click on Settings -> Control Panel and double click the ODBC icon.

2. Click on the System DSN tab and then click the Add button.

3. Choose the SQL Server driver since this will be a connection to SQL Server. Click Finish to continue with the data source definition.

4. 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.

5. I accepted all the defaults here. Click Next to continue.

6. 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.

7. I also left this window alone and clicked Finish.

8. 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.

9. 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.

10. 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.

2. Create a Heterogeneous Services Initialization File

Oracle has provided a sample heterogeneous services init file within the $ORACLE_HOME/hs/admin directory. You will need to copy that file to a new file name within the same directory and edit it for the ODBC DSN you have just created. Below you will find the sample heterogeneous services file Oracle provides and then an edited version, which I have given a new name that corresponds to my DSN name.

$ORACLE_HOME/hs/admin/inithsodbc.ora sample file

# This is a sample agent init file that contains the HS parameters that are  needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = <odbc data_source_name>
HS_FDS_TRACE_LEVEL = <trace_level>
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

$ORACLE_HOME/hs/admin/initMYSQLSERVERDSN.ora altered file

# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MYSQLSERVERDSN
HS_FDS_TRACE_LEVEL  = OFF