Making a Connection from Oracle to SQL Server

Friday Dec 3rd 2004 by James Koopmann
Share:

SQL Server is gaining popularity and there are many shops that I know of that have this database system within their walls. Learn how to make a connection from Oracle to SQL Server using Oracles heterogeneous Services ODBC agent.

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.

Click for larger image

  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


3. Alter your listener.ora file


Here again Oracle has given us a sample listener.ora file to follow for heterogeneous services within the $ORACLE_HOME/hs/admin directory. Below you will find the sample file and the additions I made to my listener.ora file. I made five distinct changes..

  1. Created my own listener name of LISTENERMYSQLSERVERDSN
  2. Changed the Port number to 1522
  3. Changed the SID_NAME to my DSN (MYSQLSERVERDSN)
  4. Changed the ORACLE_HOME location
  5. Changed the PROGRAM to hsodbc

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

Click for full code

$ORACLE_HOME/network/admin/listener.ora altered file

LISTENERMYSQLSERVERDSN =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENERMYSQLSERVERDSN=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=MYSQLSERVERDSN)
         (ORACLE_HOME = d:\oracle\product\10.1.0\db_1)
         (PROGRAM=hsodbc)
       )
      )

4. Alter your tnsnames.ora file

Here again Oracle has given us a sample tnsnames.ora file to follow for heterogeneous services within the $ORACLE_HOME/hs/admin directory. Below you will find the sample file and the additions I made to my tnsnames.ora file.

1.      I made four distinct changes.Created a TNS entry named MYSQLSERVERDSN

2.      Changed the Port number to 1522

3.      Changed the SID to my DSN (MYSQLSERVERDSN)

4.      Added OK to the HS= parameter

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

# This is a sample tnsnames.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent

#hsagent  =
#  (DESCRIPTION=
#    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
#    (CONNECT_DATA=(SID=hsagent))
#    (HS=)
#  )

$ORACLE_HOME/network/admin/tnsnames.ora altered file

MYSQLSERVERDSN  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
    (CONNECT_DATA=(SID=MYSQLSERVERDSN))
    (HS=OK)
  )

5. Start the new Listener

This should be self-explanatory but I provide the output here so that you can know what to expect when you start yours.

C:\>lsnrctl start listenermysqlserverdsn

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 01-DEC-2004 13:19:06

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
System parameter file is d:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Log messages written to d:\oracle\product\10.1.0\db_1\network\log\listenermysqlserverdsn.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
STATUS of the LISTENER
------------------------
Alias                     listenermysqlserverdsn
Version                   TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
Start Date                01-DEC-2004 13:19:09
Uptime                    0 days 0 hr. 0 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   d:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Listener Log File         d:\oracle\product\10.1.0\db_1\network\log\listenermysqlserverdsn.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Services Summary...
Service "MYSQLSERVERDSN" has 1 instance(s).
  Instance "MYSQLSERVERDSN", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

6. Validate the connection to your DSN

You can now validate the connection to your SQL Server database by the normal Oracle tnsping utility.

C:\>tnsping mysqlserverdsn

TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 01-DEC-2004 13:19:54

Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used parameter files:
d:\oracle\product\10.1.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) 
  (CONNECT_DATA=(SID=MYSQLSERVERDSN)) (HS=OK))

OK (30 msec)

7. Create a Database Link within Your Oracle Database

Since we would like to connect from our Oracle database and select information from the SQL Server database, we need to create a database link just as if we were connecting to any other remote Oracle database. I have used the default sa login from SQL Server but you may wish to create your own.

SQL> create database link mysqlserverdsn 
     connect to sa identified by  using 'MYSQLSERVERDSN';

Database link created.

8. Select some data

This is the fun part as it is the culmination of what we were trying to do. You may describe the tables from SQL Server just as you have done with Oracle in the past and then use a SELECT command. Note that my select statement has double quotes and exact upper and lower cases for the SQL Server query. This is required, at least I tried every combination and none worked except this way.

SQL> desc employees@mysqlserverdsn
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EmployeeID                                NOT NULL NUMBER(10)
 LastName                                  NOT NULL VARCHAR2(20)
 FirstName                                 NOT NULL VARCHAR2(10)
 Title                                              VARCHAR2(30)
 TitleOfCourtesy                                    VARCHAR2(25)
 BirthDate                                          DATE
 HireDate                                           DATE
 Address                                            VARCHAR2(60)
 City                                               VARCHAR2(15)
 Region                                             VARCHAR2(15)
 PostalCode                                         VARCHAR2(10)
 Country                                            VARCHAR2(15)
 HomePhone                                          VARCHAR2(24)
 Extension                                          VARCHAR2(4)
 Photo                                              LONG RAW
 Notes                                              LONG
 ReportsTo                                          NUMBER(10)
 PhotoPath                                          VARCHAR2(32512 CHAR)

SQL> select "EmployeeID","LastName","FirstName" from employees@mysqlserverdsn;

EmployeeID LastName             FirstName
---------- -------------------- ----------
         1 Davolio              Nancy
         2 Fuller               Andrew
         3 Leverling            Janet
         4 Peacock              Margaret
         5 Buchanan             Steven
         6 Suyama               Michael
         7 King                 Robert
         8 Callahan             Laura
         9 Dodsworth            Anne

9 rows selected. 

I have to say this was fun, although it took me quite a while to figure some of the specifics and nuances of this type of connection. I hope that you will not need to spend as much time as I since I have gone through some of the headaches. I would also suggest you read some of the Oracle manual "Heterogeneous Connectivity Administrator's Guide" and look at some of the particulars and issues around interacting with SQL Server through this ODBC connection. Happy SELECTing.

» See All Articles by Columnist James Koopmann

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved