How to setup database link from Oracle to SQL Server database using Heterogeneous Services ?

You can review the following document to access tables in the MSSQL database using the DBLINK architecture over the Oracle database. Please try the relevant work on the TEST database first.

In order to perform the relevant operation, the “Microsoft® ODBC Driver 11 for SQL Server® – Red Hat Linux” package must be downloaded using the link below.

http://www.microsoft.com/en-us/download/confirmation.aspx?id=36437

Copy tar file to the server where the Oracle database is installed using wget/upload. Then extract tar file that run build_md.sh to build MS ODBC Driver package.

[root@db msodbcsql-11.0.2270.0]# tar -zxvf msodbcsql-11.0.2270.0.tar
[root@db msodbcsql-11.0.2270.0]# ./build_dm.sh

Build unixODBC 2.3.0 DriverManager script
Copyright Microsoft Corp.

In order to use the Microsoft ODBC Driver 11 for SQL Server on Linux,
the unixODBC DriverManager must be installed on your computer. unixODBC
DriverManager is a third-party tool made available by the unixODBC Project.
To assist you in the installation process, this script will attempt to
Download, properly configure, and build the unixODBC DriverManager from
http://www.unixodbc.org/ for use with the Microsoft ODBC Driver 11
for SQL Server ODBC Driver on Linux.

Alternatively, you can choose to download and configure unixODBC
DriverManager from http://www.unixodbc.org/ yourself.

Note: unixODBC DriverManager is licensed to you under the terms of an
Agreement between you and the unixODBC Project, not Microsoft. Microsoft
does not guarantee the unixODBC DriverManager or grant any rights to
you Prior to downloading, you should review the license for unixODBC
DriverManager at http://www.unixodbc.org/.

The script is provided as a convenience to you as-is, without any express
or implied warranties of any kind. Microsoft is not liable for any issues
arising out of your use of the script.

Enter 'YES' to have this script continue: YES

Verifying processor and operating system ................................... OK
Verifying wget is installed ............................................. ..OK
Verifying tar is installed ................................................. ...OK
Verifying make is installed ............................................. ..OK
Downloading unixODBC 2.3.0 DriverManager ............................... OK
Unpacking unixODBC 2.3.0 DriverManager ............................................ OK
Configuring unixODBC 2.3.0 DriverManager ............................... OK
Building unixODBC 2.3.0 DriverManager .................................. OK
Build of the unixODBC 2.3.0 DriverManager complete.

Run the command 'cd /tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0; make install' to install the driver manager.

PLEASE NOTE THAT THIS WILL POTENTIALLY INSTALL THE NEW DRIVER MANAGER OVER ANY
EXISTING UNIXODBC DRIVER MANAGER. IF YOU HAVE ANOTHER COPY OF UNIXODBC INSTALLED,
THIS MAY POTENTIALLY OVERWRITE THAT COPY.
[root@TestOracle01 msodbcsql-11.0.2270.0]#
[root@TestOracle01 msodbcsql-11.0.2270.0]# cd /tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0;
[root@TestOracle01 unixODBC-2.3.0]# make install
Installing extras
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/extras'
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/extras'
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/extras'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/extras'
Making an installation in log
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/log'
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/log'
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/log'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/log'
Making install in list
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/lst'
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/lst'
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/lst'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/lst'
Making an install ini
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/ini'
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/ini'
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/ini'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/ini'
Installing libltdl
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/libltdl'
make install-am
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/libltdl'
make[3]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/libltdl'
test -z "/usr/lib64" || /bin/mkdir -p "/usr/lib64"
test -z "/usr/include" || /bin/mkdir -p "/usr/include"
test -z "" || /bin/mkdir -p ""
make[3]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/libltdl'
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/libltdl'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/libltdl'
Installing in odbcinst
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/odbcinst'
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/odbcinst'
test -z "/usr/lib64" || /bin/mkdir -p "/usr/lib64"
 /bin/sh ../libtool --mode=install /usr/bin/install -c libodbcinst.la '/usr/lib64'
libtool: install: /usr/bin/install -c .libs/libodbcinst.so.1.0.0 /usr/lib64/libodbcinst.so.1.0.0
libtool: install: (cd /usr/lib64 && { ln -s -f libodbcinst.so.1.0.0 libodbcinst.so.1 || { rm -f libodbcinst.so.1 && ln -s libodbcinst.so.1.0. 0 libodbcinst.so.1; }; })
libtool: install: (cd /usr/lib64 && { ln -s -f libodbcinst.so.1.0.0 libodbcinst.so || { rm -f libodbcinst.so && ln -s libodbcinst.so.1.0.0 libodbcinst.so ; }; })
libtool: install: /usr/bin/install -c .libs/libodbcinst.lai /usr/lib64/libodbcinst.la
libtool: finish: PATH="/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin :/root/bin:/sbin" ldconfig -n /usr/lib64
-------------------------------------------------- --------------------
Libraries have been installed in:
   /usr/lib64

If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR'
flag during linking and do at least one of the following:
   - add LIBDIR to the `LD_LIBRARY_PATH' environment variable
     during execution
   - add LIBDIR to the `LD_RUN_PATH' environment variable
     during linking
   - use the `-Wl,-rpath -Wl,LIBDIR' linker flag
   - have your system administrator add LIBDIR to `/etc/ld.so.conf'

See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.
-------------------------------------------------- --------------------
test -z "/etc" || /bin/mkdir -p "/etc"
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/odbcinst'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/odbcinst'
Making install in DriverManager
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/DriverManager'
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/DriverManager'
test -z "/usr/lib64" || /bin/mkdir -p "/usr/lib64"
 /bin/sh ../libtool --mode=install /usr/bin/install -c libodbc.la '/usr/lib64'
libtool: install: /usr/bin/install -c .libs/libodbc.so.1.0.0 /usr/lib64/libodbc.so.1.0.0
libtool: install: (cd /usr/lib64 && { ln -s -f libodbc.so.1.0.0 libodbc.so.1 || { rm -f libodbc.so.1 && ln -s libodbc.so.1.0. 0 libodbc.so.1; }; })
libtool: install: (cd /usr/lib64 && { ln -s -f libodbc.so.1.0.0 libodbc.so || { rm -f libodbc.so && ln -s libodbc.so.1.0.0 libodbc.so ; }; })
libtool: install: /usr/bin/install -c .libs/libodbc.lai /usr/lib64/libodbc.la
libtool: finish: PATH="/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin :/root/bin:/sbin" ldconfig -n /usr/lib64
-------------------------------------------------- --------------------
Libraries have been installed in:
   /usr/lib64

If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR'
flag during linking and do at least one of the following:
   - add LIBDIR to the `LD_LIBRARY_PATH' environment variable
     during execution
   - add LIBDIR to the `LD_RUN_PATH' environment variable
     during linking
   - use the `-Wl,-rpath -Wl,LIBDIR' linker flag
   - have your system administrator add LIBDIR to `/etc/ld.so.conf'

See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.
-------------------------------------------------- --------------------
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/DriverManager'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/DriverManager'
Making install in exe
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/exe'
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/exe'
test -z "/usr/bin" || /bin/mkdir -p "/usr/bin"
  /bin/sh ../libtool --mode=install /usr/bin/install -c isql dltest odbcinst iusql odbc_config '/usr/bin'
libtool: install: /usr/bin/install -c .libs/isql /usr/bin/isql
libtool: install: /usr/bin/install -c dltest /usr/bin/dltest
libtool: install: /usr/bin/install -c .libs/odbcinst /usr/bin/odbcinst
libtool: install: /usr/bin/install -c .libs/iusql /usr/bin/iusql
libtool: install: /usr/bin/install -c odbc_config /usr/bin/odbc_config
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/exe'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/exe'
Installing in cur
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/cur'
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/cur'
test -z "/usr/lib64" || /bin/mkdir -p "/usr/lib64"
 /bin/sh ../libtool --mode=install /usr/bin/install -c libodbccr.la '/usr/lib64'
libtool: install: /usr/bin/install -c .libs/libodbccr.so.1.0.0 /usr/lib64/libodbccr.so.1.0.0
libtool: install: (cd /usr/lib64 && { ln -s -f libodbccr.so.1.0.0 libodbccr.so.1 || { rm -f libodbccr.so.1 && ln -s libodbccr.so.1.0. 0 libodbccr.so.1; }; })
libtool: install: (cd /usr/lib64 && { ln -s -f libodbccr.so.1.0.0 libodbccr.so || { rm -f libodbccr.so && ln -s libodbccr.so.1.0.0 libodbccr.so ; }; })
libtool: install: /usr/bin/install -c .libs/libodbccr.lai /usr/lib64/libodbccr.la
libtool: finish: PATH="/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin :/root/bin:/sbin" ldconfig -n /usr/lib64
-------------------------------------------------- --------------------
Libraries have been installed in:
   /usr/lib64

If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR'
flag during linking and do at least one of the following:
   - add LIBDIR to the `LD_LIBRARY_PATH' environment variable
     during execution
   - add LIBDIR to the `LD_RUN_PATH' environment variable
     during linking
   - use the `-Wl,-rpath -Wl,LIBDIR' linker flag
   - have your system administrator add LIBDIR to `/etc/ld.so.conf'

See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.
-------------------------------------------------- --------------------
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/cur'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/cur'
Installing DRVConfig
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/DRVConfig'
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/DRVConfig'
make[3]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/DRVConfig'
make[3]: Nothing to be done for `install-exec-am'.
make[3]: Nothing to be done for `install-data-am'.
make[3]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/DRVConfig'
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/DRVConfig'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/DRVConfig'
Installing Drivers
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/Drivers'
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/Drivers'
make[3]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/Drivers'
make[3]: Nothing to be done for `install-exec-am'.
make[3]: Nothing to be done for `install-data-am'.
make[3]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/Drivers'
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/Drivers'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/Drivers'
Making an installation included
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/include'
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/include'
make[2]: Nothing to be done for `install-exec-am'.
test -z "/usr/include" || /bin/mkdir -p "/usr/include"
 /usr/bin/install -c -m 644 odbcinst.h odbcinstext.h sql.h sqlext.h sqltypes.h sqlucode.h uodbc_stats.h uodbc_extras.h '/usr/include'
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/include'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/include'
Making an installation in doc
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc'
Making install in AdministratorManual
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/AdministratorManual'
make[3]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/AdministratorManual'
make[3]: Nothing to be done for `install-exec-am'.
make[3]: Nothing to be done for `install-data-am'.
make[3]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/AdministratorManual'
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/AdministratorManual'
Making install in ProgrammerManual
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/ProgrammerManual'
How to Install in Tutorial
make[3]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/ProgrammerManual/Tutorial'
make[4]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/ProgrammerManual/Tutorial'
make[4]: Nothing to be done for `install-exec-am'.
make[4]: Nothing to be done for `install-data-am'.
make[4]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/ProgrammerManual/Tutorial'
make[3]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/ProgrammerManual/Tutorial'
make[3]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/ProgrammerManual'
make[4]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/ProgrammerManual'
make[4]: Nothing to be done for `install-exec-am'.
make[4]: Nothing to be done for `install-data-am'.
make[4]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/ProgrammerManual'
make[3]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/ProgrammerManual'
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/ProgrammerManual'
Making installation in UserManual
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/UserManual'
make[3]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/UserManual'
make[3]: Nothing to be done for `install-exec-am'.
make[3]: Nothing to be done for `install-data-am'.
make[3]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/UserManual'
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/UserManual'
Making install in list
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/lst'
make[3]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/lst'
make[3]: Nothing to be done for `install-exec-am'.
make[3]: Nothing to be done for `install-data-am'.
make[3]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/lst'
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc/lst'
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc'
make[3]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc'
make[3]: Nothing to be done for `install-exec-am'.
make[3]: Nothing to be done for `install-data-am'.
make[3]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc'
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/doc'
Installing samples
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/samples'
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/samples'
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/samples'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0/samples'
make[1]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0'
make[2]: Entering directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0'
make[2]: Nothing to be done for `install-exec-am'.
touch /etc/odbcinst.ini
touch /etc/odbc.ini
mkdir -p /etc/ODBCDataSources
/usr/bin/odbc_config --header > /usr/include/unixodbc_conf.h
make[2]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0'
make[1]: Leaving directory `/tmp/unixODBC.26962.10505.13318/unixODBC-2.3.0'

Check whether the relevant package is healthy,

 

[root@db msodbcsql-11.0.2270.0]# ./install.sh verify

Microsoft ODBC Driver 11 for SQL Server Installation Script
Copyright Microsoft Corp.

Starting install for Microsoft ODBC Driver 11 for SQL Server

Checking for 64 bit Linux compatible OS ............................................ OK
Checking required libs are installed ............................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................ OK
unixODBC Driver Manager configuration correct ............................. OK*
Microsoft ODBC Driver 11 for SQL Server already installed ............ NOT FOUND

Install log created at /tmp/msodbcsql.5731.31852.9475/install.log.

One or more steps may have an *. See README for more information regarding
these steps.

 

The part that says NOT FOUND is the package we will use. We haven’t installed it yet. For the relevant package installation :

[root@db msodbcsql-11.0.2270.0]# ./install.sh install

Enter YES to accept the license or anything else to terminate the installation: YES

Checking for 64 bit Linux compatible OS ............................................ OK
Checking required libs are installed ............................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................ OK
unixODBC Driver Manager configuration correct ............................. OK*
Microsoft ODBC Driver 11 for SQL Server already installed ............ NOT FOUND
Microsoft ODBC Driver 11 for SQL Server files copied ........................ OK
Symbolic links for bcp and sqlcmd created ................................... OK
Microsoft ODBC Driver 11 for SQL Server registered ............... INSTALLED

 

We can test the package installation with verify again.

 

[root@db msodbcsql-11.0.2270.0]# ./install.sh verify

Microsoft ODBC Driver 11 for SQL Server Installation Script
Copyright Microsoft Corp.

Starting install for Microsoft ODBC Driver 11 for SQL Server

Checking for 64 bit Linux compatible OS ............................................ OK
Checking required libs are installed ............................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................ OK
unixODBC Driver Manager configuration correct ............................. OK*
Microsoft ODBC Driver 11 for SQL Server already installed ............ INSTALLED

See /tmp/msodbcsql.23248.18969.28756/install.log for more information about installation failures.

 

Check the version information of the package :

[root@db msodbcsql-11.0.2270.0]# odbc_config --version
2.3.0[root@db msodbcsql-11.0.2270.0]# odbc_config --version
2.3.0

Check configuration information. /etc/odbc.ini will be the configuration file we will use most.

[root@TestOracle01 msodbcsql-11.0.2270.0]# odbcinst -j
unixODBC 2.3.0
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Check the driver information:

[root@db msodbcsql-11.0.2270.0]# odbcinst -q -d -n "ODBC Driver 11 for SQL Server"
[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1

After these checks, we can now use the relevant packages to connect from Oracle to MSSQL.

Configuring MSSQL Connection Information

We need to enter information about the MSSQL database to be connected into the /etc/odbc.ini file.

The point to note here is that the type of database we added under [ODBC Data Sources] must be specified. If more than one will be used, it needs to be processed on a new line.

[MSSQL_DB_NAME] > The information that will be used to access the MSSQL database that we defined as

[root@db ~]# cat /etc/odbc.ini
[ODBC Data Sources]
MSSQL_DB_NAME=MS SQL Server

[MSSQL_DB_NAME]
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Description=Microsoft ODBC Driver 11 for SQL Server
Server=tcp:<IP_ADDRESS>,<PORT>
Database=<DB_NAME>
User=username
Password=password
QuotedId=YES
AnsiNPW=YES
Threading=1
UsageCount=1
AutoTranslate=No

Above you see an image of a sample odbc.ini file.

In order for the [MSSQL_DB_NAME] definition to be meaningful to ORACLE, we will perform the following operations.

[root@db ~]# su - oracle
[oracle@db ~]$ cd /u01/app/oracle/product/11.2.0.4/db_1/hs/admin/
[oracle@db admin]$ ls -ltr
Total 12
-rw-r--r-- 1 oracle oinstall 489 Aug 24 2013 initdg4odbc.ora
-rw-r--r-- 1 oracle oinstall 1109 Aug 24 2013 extproc.ora

In order for MSSQL_DB_NAME to be recognized by ORACLE under the directory, the critical point here is that the name of the .ora file must be the same as the definition you made in the .ini file and must start with init….

[oracle@db admin]$ vi init<MSSQL_DB_NAME>.ora
HS_FDS_CONNECT_INFO = MSSQL_DB_NAME
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_TRACE_LEVEL = 0
HS_LANGUAGE = AMERICAN_AMERICA.WE8MSWIN1252
HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL
HS_NLS_LENGTH_SEMANTICS = CHAR
HS_NLS_NCHAR = UCS2

To access this odbc.ini file by the environment,

set ODBCINI=/etc/odbc.ini

If all the steps are correct, we will be able to connect to the MSSQL database with the isql tool. NOTE: Password must be written in single quotes (‘). If there is a single quote in the password, there is a problem.

[oracle@db ~]$ isql -v <MSSQL_DB_NAME> <USERNAME> <PASSWORD>
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+

If you haven’t had any problems up to this point, let’s enable ORACLE to access this service via DBLINK.

[oracle@db admin]$ cd /u01/app/oracle/product/11.2.0.4/db_1/network/admin

<MSSQL_DB_NAME> =
   (DESCRIPTION =
      (ADDRESS =
         (PROTOCOL = tcp)
         (HOST = <oracle database host information> )
         (PORT = 1521))
      (CONNECT_DATA =
         (SID = <MSSQL_DB_NAME> ))
      (HS = OK)
   )

tthe configuration we made for MSSQL as if it were a service provided by ORACLE is below

[oracle@db admin]$ cd /u01/app/11.2.0.4/grid/network/admin

MSSQL_DB_NAME =
   (DESCRIPTION =
      (ADDRESS =
         (PROTOCOL = tcp)
         (HOST = <oracle database host information>)
         (PORT = 1521))
      (CONNECT_DATA =
         (SID = <MSSQL_DB_NAME>))
      (HS = OK)
   )

NOTE: If you are using an Oracle database in the RAC architecture, the relevant operation must be provided on each NODE.

After entering the relevant information, a LISTENER definition must be added.

SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC =
         (SID_NAME = <MSSQL_DB_NAME>)
         (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
         (PROGRAM = /u01/app/oracle/product/11.2.0.4/db_1/bin/dg4odbc)
         (ENVS=LD_LIBRARY_PATH=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0:/usr/lib64:/u01/app/oracle/product/11.2.0.4/db_1/lib)
      )
   )

We learned the LD_LIBRARY_PATH information before during the package installation.

Let’s define DBLINK on the Oracle database.

CREATE PUBLIC DATABASE LINK <MSSQL_DB_NAME>
 CONNECT TO <USERNAME>
 IDENTIFIED BY <PASSWORD>
 USING '<MSSQL_DB_NAME';

In the USING section, we will use the service name we defined in odbc.ini.

SELECT SYSDATE FROM DUAL@<MSSQL_DB_NAME;

Access to MSSQL from Oracle will be provided by displaying the relevant result. Please note that the most important part to pay attention to is the service naming and access naming.