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.
- From the start menu click on
Settings -> Control Panel and double click the ODBC icon.
- Click on the System DSN tab
and then click the Add button.
- Choose the SQL Server driver
since this will be a connection to SQL Server. Click Finish to continue
with the data source definition.
- 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.
- I accepted all the defaults
here. Click Next to continue.
- 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.
- I also left this window alone
and clicked Finish.
- 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.
- 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.
- 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
