Apr 18 2013

Oracle DbLink With Odbc Connection From Oracle to MsSQL Server With FreeTDS

Category: Administration,Linux & Unix,Procedure,SQLFatih Acar @ 14:08

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

dblink

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

Download Page, Direct Download

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’;

116,677 total views, 18 views today

Tags: FreeTDS ODBC Connection, Linux Administration, Oracle, Oracle Administration, System Administration

facebook comments:

5 Responses to “Oracle DbLink With Odbc Connection From Oracle to MsSQL Server With FreeTDS”

  1. Krishna says:

    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

    • Bora Aydemir says:

      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.

  2. Bora Aydemir says:

    Shouldn’t the filename at “Configure init” be initSQLDB.ora instead of initSQLDB.ini?

  3. Manoj says:

    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

  4. Linda says:

    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

Leave a Reply to Manoj

Time limit is exhausted. Please reload CAPTCHA.