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.

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.
  1. Click on the System DSN tab
    and then click the Add button.

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

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

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

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

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

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

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

  1. 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
James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles