The connectivity between Oracle and MS SQL database is established using unixODBC and FreeTDS.
What you need?
Platform:
Fedora Core 6
Oracle Database
10g R2
MS SQL server 2000
database
Packages:
unixODBC
FreeTDS
Download URL:
http://www.unixodbc.org/
http://freetds.org/
If you want to install unixODBC with GUI option then you will also need Qt package. You can download it at the following link.
http://trolltech.com/developer/downloads/qt/x11
In my setup process, I didn't use GUI to setup DSN. If you don't know what I am talking about the bear with me for next few minutes.
The software version that I used are unixODBC-2.2.12.tar.gz and freetds-stable.tgz.
Setup 1: Install unixODBC package
Install unixODBC first. Download the software in your home directory or anywhere you like and uncompress and untar the software.
You can do this by using following command
# tar -xvzf unixODBC-2.2.12.tar.gz
Once it is done, go to the unixODBCxxxx dir and run the configure command.
# ./configure –prefix=/usr/local –enable-gui=no
Note: If you dont use enable-gui option and dont have Qt package, you will get the error. So make sure you use it if no Qt is installed.
After configure is successfully completed, run the make and then make install commands.
# make
# make install
Step 2: Install freeTDS package
Download the freeTDS package and untar and uncompress it.
# tar -xvzf freetds-stable.tgz
Run the configure.
# ./configure –with-tdsver=8.0 –with-unixODBC=/usr/local
Run the make
# make
Install it
# make install
Step 3: Configuration - freeTDS
Start configuration with freeTDS. Look for the freetds.conf file and add the entry for the MS SQL server.
[MSTEST]
host = 192.168.1.100
port = 1433
tds version = 8.0
Sql server standard port is the 1433. The tds version for MS SQL server 2000 is 8. You can try 8.0 with new version as well.
Step 4: Configuration – unixODBC
The unixODBC need two main configuration files called odbcinst.ini and odbc.ini. These files should be in the /etc/ dir.
I. The first file odbcinst.ini contain the definition of ODBC driver.
[TDS]
Description = FreeTDS driver
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so
Trace = Yes
TraceFile = /tmp/freetds.log
FileUsage = 1
Note: Before making above entries, make sure libtdsodbc.so exists in the /usr/local/lib dir.
In the above configuration, we have define TDS as driver.
II. The second file is odbc.ini. This file has information about your MS SQL database.
[MSTEST]
Driver = TDS
Description = MS SQL Test
Trace = Yes
TraceFile = /tmp/mstest.log
Servername = 192.168.1.100
Database = testdb
Port = 1433
You can notice above that the freeTDS driver is the name defined in odbcinst.ini while servername is the one defined in freetds.conf.
Step 5: Test MS SQL connectivity from Linux box
Check if you can connect to MS SQL database using unixODBC tool called isql.
# isql -v mstest satest satest
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>select * from "sysObjects";
If you see the results, you are connected.
Step 6: Configuring Oracle heterogeneous services to work with MS SQL
I. Create init<sid>.ora file
Create a init.ora file in your $ORACLE_HOME/hs/admin dir. There should be a file called inithsodbc.ora in the directory. Copy this file into a file called initmstest.ora as our sid here is mstest and edit it.
# 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 = MSTEST
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = /tmp/freetds.trc
HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/usr/local/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
Please note that the parameter HS_FDS_CONNECT_INFO should be set to your DSN name.
II. Add listener entry
Open your listener.ora file add new listener entry.
SID_LIST_MSTEST =
(SID_LIST =
(SID_DESC =
(SID_NAME = mstest)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = hsodbc)
)
)
MSTEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
)
)
After this entry is added, start the listener mstest.
# lsnrctl start mstest
III. Create a TNS entry.
MSTEST =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(CONNECT_DATA=(SID=MSTEST))
(HS=OK)
)
Test the TNS connecivity using tnsping.
# tnsping mstest
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 29-JUN-2007 14:57:57
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/10.2.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=MSTEST)) (HS=OK))
OK (30 msec)
IV. Create the database link in Oracle to connect to MS SQL
Connect to a user in Oracle database and create the database link
SQL> create database link mstest connect to user identified by password using 'MSTEST';
After link is created, you should be able to select data from MS SQL table.
SQL> select * from "sysObects"@mstest;
That is all. If this works, you are done.
Please note that MS SQL table name are case sensitive so make sure you use double quotes around the table/column name.
Troubleshooting:
Error: ORA-28511: lost RPC connection to heterogeneous remote agent using SID=%s
SQL> select * from sysobjects@mstest;
select * from sysobjects@mstest
*
ERROR at line 1:
ORA-02068: following severe error from MSTEST
ORA-28511: lost RPC connection to heterogeneous remote agent using SID=%s
ORA-28509: unable to establish a connection to non-Oracle system
Cause: This error means your library path is incorrect.
Solution: Check the library path in your ODBC.ini file. You make also need to add ENV=LD_LIBRARY_PATH= in your listener.ora file.
Full article...
