{"id":768,"date":"2013-04-18T14:08:42","date_gmt":"2013-04-18T11:08:42","guid":{"rendered":"http:\/\/www.fatihacar.com\/blog\/?p=768"},"modified":"2019-10-03T14:35:56","modified_gmt":"2019-10-03T11:35:56","slug":"oracle-dblink-with-odbc-connection-from-oracle-to-mssql-server-with-freetds","status":"publish","type":"post","link":"http:\/\/www.fatihacar.com\/blog\/oracle-dblink-with-odbc-connection-from-oracle-to-mssql-server-with-freetds\/","title":{"rendered":"Oracle DbLink With Odbc Connection From Oracle to MsSQL Server With FreeTDS"},"content":{"rendered":"<p>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.<\/p>\n<p>I made test below systems.<\/p>\n<p><strong>Systems<\/strong><\/p>\n<ul>\n<li>Oracle Linux 6.x \/ 7.x<\/li>\n<li>Oracle 11g \/ 12c<\/li>\n<li>Windows Server 2008 R2<\/li>\n<li>MsSQL Server 2008 R2<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1484\" src=\"http:\/\/www.fatihacar.com\/blog\/resimler\/dblink.jpg\" alt=\"dblink\" width=\"654\" height=\"335\" srcset=\"http:\/\/www.fatihacar.com\/blog\/resimler\/dblink.jpg 654w, http:\/\/www.fatihacar.com\/blog\/resimler\/dblink-300x154.jpg 300w\" sizes=\"auto, (max-width: 654px) 100vw, 654px\" \/><\/p>\n<p><strong>Steps;<\/strong><\/p>\n<ul>\n<li>Download freetds-stable program<\/li>\n<li>Install unixODBC and unixODBC-devel<\/li>\n<li>Install freetds<\/li>\n<li>Configure freetds.conf<\/li>\n<li>Configure odbc.ini<\/li>\n<li>Configure init<\/li>\n<li>Configure Listener<\/li>\n<li>Configure tnsnames<\/li>\n<li>Create DbLink<\/li>\n<\/ul>\n<p><strong>Download freetds-stable<\/strong><\/p>\n<blockquote><p><a href=\"http:\/\/mirrors.ibiblio.org\/freetds\/stable\/\" target=\"_blank\" rel=\"noopener noreferrer\">Download Page<\/a>, <a href=\"http:\/\/mirrors.ibiblio.org\/freetds\/stable\/freetds-stable.tgz\" target=\"_blank\" rel=\"noopener noreferrer\">Direct Download<\/a><\/p><\/blockquote>\n<p><strong>Install unixODBC-devel<\/strong><\/p>\n<blockquote><p>yum install unixODBC<br \/>\nyum install unixODBC-devel<\/p><\/blockquote>\n<p><strong>Install Development Tools<\/strong><\/p>\n<blockquote><p>yum groupinstall &#8220;Development Tools&#8221;<\/p><\/blockquote>\n<p><strong>Install freeTDS<\/strong><\/p>\n<blockquote><p>tar zfvx freetds-stable.tgz<br \/>\ncd freetds-0.91<br \/>\n.\/configure &#8211;prefix=\/usr\/local\/freetds &#8211;with-tdsver=8.0 &#8211;enable-msdblib &#8211;with-gnu-ld<br \/>\nmake<br \/>\nmake install<\/p><\/blockquote>\n<p><!--more--><br \/>\n<strong>Configure freetds.conf<\/strong><\/p>\n<blockquote><p>vi \/usr\/local\/freetds\/etc\/freetds.conf<\/p>\n<p>[SQLSERVERADDRESS]<\/p>\n<p>host = 192.168.10.10 # MsSQL Server IP Address<br \/>\nport = 1433 # MsSQL Server Port<br \/>\ntds version = 8.0 # Tds version for SQL Server 2008<br \/>\nclient charset = ISO-8859-9 # For support of Turkish characters<\/p><\/blockquote>\n<p><strong>Configure odbc.ini<\/strong><\/p>\n<blockquote><p>vi \/etc\/odbc.ini<\/p>\n<p>[SQLDSN]<br \/>\nDescription = SQLDSN CONNECTION<br \/>\nDriver = \/usr\/local\/freetds\/lib\/libtdsodbc.so<br \/>\nServername = SQLSERVERADDRESS<br \/>\nDatabase = DBNAME<\/p>\n<p>[ODBC Data Sources]<br \/>\nSQLDSN=FreeTDS<\/p><\/blockquote>\n<p><strong>Configure init<\/strong><\/p>\n<blockquote><p>vi $ORACLE_HOME\/hs\/admin\/initSQLDB.ora<\/p>\n<p>HS_FDS_CONNECT_INFO = SQLDSN<br \/>\nHS_FDS_TRACE_LEVEL = 0<br \/>\nHS_FDS_SHAREABLE_NAME = \/usr\/lib64\/libodbc.so<br \/>\nHS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9<br \/>\nset ODBCINI=\/etc\/odbc.ini<\/p><\/blockquote>\n<p><strong>Configure Listener<\/strong><\/p>\n<blockquote><p>vi $ORACLE_HOME\/network\/admin\/listener.ora<\/p>\n<p>If you are using grid infrastructure, you have to edit grid&#8217;s listener in grid home.<br \/>\nAdd below lines.<\/p>\n<p>SID_LIST_LISTENER =<br \/>\n(SID_LIST =<br \/>\n(SID_DESC =<br \/>\n(SID_NAME=SQLDB)<br \/>\n(ORACLE_HOME=$ORACLE_HOME)<br \/>\n(PROGRAM=dg4odbc)<br \/>\n(ENVS= LD_LIBRARY_PATH=\/usr\/lib64:\/usr\/local\/freetds\/lib:$ORACLE_HOME\/lib)<br \/>\n)<br \/>\n)<\/p><\/blockquote>\n<p><strong>Configure tnsnames<\/strong><\/p>\n<blockquote><p>vi $ORACLE_HOME\/network\/admin\/tnsnames.ora<\/p>\n<p>Add below lines.<\/p>\n<p>SQLDB =<br \/>\n(DESCRIPTION =<br \/>\n(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))<br \/>\n(CONNECT_DATA =<br \/>\n(SID = SQLDB)<br \/>\n)<br \/>\n(HS = OK)<br \/>\n)<\/p><\/blockquote>\n<p><strong>Create DbLink<\/strong><\/p>\n<blockquote><p>SQL&gt; CREATE DATABASE LINK DBLINKNAME CONNECT TO &#8220;SQLSERVERUSERNAME&#8221; IDENTIFIED BY &#8220;PASSWORD&#8221; USING &#8216;SQLDB&#8217;;<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>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,&#8230;<\/p>\n","protected":false},"author":37,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[9,18,12,6],"tags":[40,31,90,32,22],"class_list":["post-768","post","type-post","status-publish","format-standard","hentry","category-administration","category-linux-unix","category-oracle-procedure","category-oracle-sql","tag-freetds-odbc-connection","tag-linux-administration","tag-oracle","tag-oracle-administration","tag-system-administration"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p39NFI-co","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/768","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/comments?post=768"}],"version-history":[{"count":12,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/768\/revisions"}],"predecessor-version":[{"id":2609,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/768\/revisions\/2609"}],"wp:attachment":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media?parent=768"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/categories?post=768"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/tags?post=768"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}