Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
You can read this document to access the tables in the MSSQL database using the DBLINK architecture from the Oracle database. Please test in the TEST database first. If you want to connect to other databases or connect to Oracle from other databases, you should read the below articles.
Connect To Oracle From SQL Server
“How To Create a Linked Server To Connect To Oracle”,
Connect To PostgreSQL From SQL Server
“How To Create a Linked Server To Connect To PostgreSQL From SQL Server”
Connect to Oracle From PostgreSQL
“Foreign Data Wrapper Between PostgreSQL and Oracle”
Connect to SQL Server From PostgreSQL
“Foreign Data Wrapper Between PostgreSQL and MSSQL”
Downlaod Package
Download the “Microsoft® ODBC Driver 11 for SQL Server® – Red Hat Linux” package using the link below.
http://www.microsoft.com/en-us/download/confirmation.aspx?id=36437
The relevant file is transferred to the server where the oracle database is installed with wget/upload.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 | [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 Making install in 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 install 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 lst 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 install in 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' Making install in 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' Making install 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' Making install 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' Making install in 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' Making install in 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 install in include 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 install 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' Making 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 install 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 lst 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' Making install in 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' |
Verify the Package
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [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 writes NOT FOUND is the package we’re going to use. We haven’t install it yet.
Install the Package
1 2 3 4 5 6 7 8 9 10 11 12 13 | [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 |
Verify the Installation
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [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 Package Version
1 2 | [root@db msodbcsql-11.0.2270.0]# odbc_config --version 2.3.0 |
Access to Configuration Information
/etc/odbc.ini will be the most commonly used configuration file.
1 2 3 4 5 6 7 8 9 | [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 |
Access to Driver Information
1 2 3 4 5 6 | [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.
Configure Heterogeneous Connection From Oracle to SQL Server
We need to write the information of the MSSQL database to be connected to /etc/odbc.ini.
The point to note here is to specify the type of database we have added under [ODBC Data Sources]. If more than one is used, it must be written to the new line.
The information you see under [MSSQL_DB_NAME] is the information used to access the MSSQL database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [root@mhrstestdb2 ~]# 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_ADRESI>,<PORT> Database=<DB_NAME> User=username Password=password QuotedId=YES AnsiNPW=YES Threading=1 UsageCount=1 AutoTranslate=No |
You see an example odbc.ini file above.
We will do the following to make [MSSQL_DB_NAME] meaningful by ORACLE.
1 2 3 4 5 6 | [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, the critical point here is that the name of the .ora file must match the definition you make in the .ini file and begin with init….
1 2 3 4 5 6 7 8 | [oracle@mhrstestdb2 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
1 | set ODBCINI=/etc/odbc.ini |
Connect to MSSQL from Oracle
If all 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 quotation mark inside the password, it is a problem.
1 2 3 4 5 6 7 8 9 | [oracle@db ~]$ isql -v <MSSQL_DB_NAME> <USERNAME> <PASSWORD> +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ |
If there is no problem up to this point, let Oracle Access this service via DBLINK.
1 2 3 4 5 6 7 8 9 10 11 12 | [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 veri tabanının host bilgisi> ) (PORT = 1521)) (CONNECT_DATA = (SID = <MSSQL_DB_NAME> )) (HS = OK) ) |
We make the configuration for MSSQL look like it’s a service offered by ORACLE.
1 2 3 4 5 6 7 8 9 10 11 12 | [oracle@db admin]$ cd /u01/app/11.2.0.4/grid/network/admin MSSQL_DB_NAME = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = <oracle veri tabanının host bilgisi>) (PORT = 1521)) (CONNECT_DATA = (SID = <MSSQL_DB_NAME>)) (HS = OK) ) |
NOTE: If you are using an Oracle database in the RAC architecture, the corresponding operation must be performed on each NODE.
Then, the LISTENER must be defined.
1 2 3 4 5 6 7 8 9 | 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 previously learned LD_LIBRARY_PATH during package installation.
Create DBLINK on Oracle database
1 2 3 4 | CREATE PUBLIC DATABASE LINK <MSSQL_DB_NAME> CONNECT TO <USERNAME> IDENTIFIED BY <PASSWORD> USING '<MSSQL_DB_NAME'; |
1 | SELECT SYSDATE FROM DUAL@<MSSQL_DB_NAME; |
Note: Please note that service names and access names are the most important part.
