I will use Oracle 11g R2 database and SQL Server 2008 R2 on this example. If you want to connect from Oracle to SQL Server, you can use freeTDS connection library on Oracle Linux systems. If we use Windows Server for Oracle database, we can use direct Windows ODBC Driver Manager. On the other hand, we need some requirement for odbc connection on Linux System. FreeTDS is a odbc connection library for MsSQL Server and other database systems.
I made test below systems.
Systems
- Oracle Linux 6.x / 7.x
- Oracle 11g / 12c
- Windows Server 2008 R2
- MsSQL Server 2008 R2
Steps;
- Download freetds-stable program
- Install unixODBC and unixODBC-devel
- Install freetds
- Configure freetds.conf
- Configure odbc.ini
- Configure init
- Configure Listener
- Configure tnsnames
- Create DbLink
Download freetds-stable
Install unixODBC-devel
yum install unixODBC
yum install unixODBC-devel
Install Development Tools
yum groupinstall “Development Tools”
Install freeTDS
tar zfvx freetds-stable.tgz
cd freetds-0.91
./configure –prefix=/usr/local/freetds –with-tdsver=8.0 –enable-msdblib –with-gnu-ld
make
make install
Configure freetds.conf
vi /usr/local/freetds/etc/freetds.conf
[SQLSERVERADDRESS]
host = 192.168.10.10 # MsSQL Server IP Address
port = 1433 # MsSQL Server Port
tds version = 8.0 # Tds version for SQL Server 2008
client charset = ISO-8859-9 # For support of Turkish characters
Configure odbc.ini
vi /etc/odbc.ini
[SQLDSN]
Description = SQLDSN CONNECTION
Driver = /usr/local/freetds/lib/libtdsodbc.so
Servername = SQLSERVERADDRESS
Database = DBNAME[ODBC Data Sources]
SQLDSN=FreeTDS
Configure init
vi $ORACLE_HOME/hs/admin/initSQLDB.ora
HS_FDS_CONNECT_INFO = SQLDSN
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
set ODBCINI=/etc/odbc.ini
Configure Listener
vi $ORACLE_HOME/network/admin/listener.ora
If you are using grid infrastructure, you have to edit grid’s listener in grid home.
Add below lines.SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=SQLDB)
(ORACLE_HOME=$ORACLE_HOME)
(PROGRAM=dg4odbc)
(ENVS= LD_LIBRARY_PATH=/usr/lib64:/usr/local/freetds/lib:$ORACLE_HOME/lib)
)
)
Configure tnsnames
vi $ORACLE_HOME/network/admin/tnsnames.ora
Add below lines.
SQLDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SID = SQLDB)
)
(HS = OK)
)
Create DbLink
SQL> CREATE DATABASE LINK DBLINKNAME CONNECT TO “SQLSERVERUSERNAME” IDENTIFIED BY “PASSWORD” USING ‘SQLDB’;
Hello
We are seeing below error message when we try to connect using the new dblink.
————————————————————————————————————
SQL> select COUNT(*) from dbo.SP_MASTER_ELECT_LOCK@TEST2SQLDEV;
select COUNT(*) from dbo.SP_MASTER_ELECT_LOCK@TEST2SQLDEV
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from TEST2SQLDEV
————————————————————————————————————
Please advice how to proceed.
Regards,
Krishna
I had the same problem, adding
TDS_Version = 8.0
to odbcinst.ini
and
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
to initSQLDB.ora
solved my problem.
Shouldn’t the filename at “Configure init” be initSQLDB.ora instead of initSQLDB.ini?
Hi, i tried with the above steps but it is not connecting, it gives error of charset…
in this example char set is of turkish, what will be the char set to connect default oracle or SQLserver.
do you have solution to the above configuration if this gives error?.
Regards
Manoj
using FreeTDS, with following odbc.ini file, I can connect to the ‘biostorage’ sql server database, but not the ahs_iamge data source. I always run into the following error when testing tsql connection. ‘Biostorage’ is SQL Sever 2012, “image_ahs’ is SQL server 2008. Any help would be appreciated.
Thanks in advance!
Linda
oracle[]$ tsql -S xxxx -D RSHARE010T -U xxxx-P xxxx
locale is “en_US.UTF-8”
locale charset is “UTF-8”
using default charset “UTF-8”
Default database being set to RSHARE010T
Error 20009 (severity 9):
Unable to connect: Adaptive Server is unavailable or does not exist
OS error 111, “Connection refused”
There was a problem connecting to the server
[Data Sources]
biostorage = TDS connection
[ODBC]
Trace = 255
[biostorage]
Description = Biostorage SQL Server
Driver = /usr/lib64/libtdsodbc.so.0
Setup = /usr/lib64/libtdsS.so
#Setup = /usr/local/lib/libtdsS.so
Trace = No
Database = bst_amgen_interface
Server = x.x.x.x
Port = 1433
TDS_Version = 8.0
# Convert UCS-2 encoded Unicode data to UTF-8
ConvToUtf = Yes
# Also do this conversion for data passed to Unicode ODBC function (SQLxxxW) calls
ConvWToUtf = Yes
VarMaxAsLong = Yes
[image_ahs]
Description = IMAGE data SQL Server
Driver = /usr/lib64/libtdsodbc.so.0
Setup = /usr/lib64/libtdsS.so
#Setup = /usr/local/lib/libtdsS.so
Trace = No
Database = Apercio_Spectrum
Server = x.x.x.x
Port = 49230
TDS_Version = 8.0