Discussion:
odbc db link to MSSQL
Jeff Chirco
2018-07-30 16:51:24 UTC
Permalink
Anybody have success with getting ODBC driver for SQL Server installed on
Oracle Linux 7 and getting a database link to work from 12.2

When I run a select over the db link I get this error.

ORA-28500: connection from ORACLE to a non-Oracle system returned this
message:

ORA-02063: preceding line from TESTMSSQL

I didn't try the easysoft driver cause didn't want to have to buy a driver
for this. But if it is needed I will explore that option.

I installed unixODBC 2.3.6 from http://www.unixodbc.org



# remove any existing unixODBC drivers - be very careful with 'sudo rm'!

sudo rm /usr/lib64/libodbc*



# install the unixODBC driver

# note, adding "--enable-stats=no" here is not specified by Microsoft

export CPPFLAGS="-DSIZEOF_LONG_INT=8"

./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc
--enable-gui=no --enable-drivers=no --enable-iconv
--with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --enable-stats=no
1> configure_std.log 2> configure_err.log

make 1> make_std.log 2> make_err.log

sudo make install 1> makeinstall_std.log 2> makeinstall_err.log



# the Microsoft driver expects unixODBC to be here /usr/lib64/libodbc.so.1,

# so add soft links to the '.so.2' files

cd /usr/lib64

sudo ln -s libodbccr.so.2 libodbccr.so.1

sudo ln -s libodbcinst.so.2 libodbcinst.so.1

sudo ln -s libodbc.so.2 libodbc.so.1



Then I downloaded Microsoft ODBC Driver 17 from
https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

sudo su

#Download appropriate package for the OS version

#Choose only ONE of the following, corresponding to your OS version

#RedHat Enterprise Server 7

curl https://packages.microsoft.com/config/rhel/7/prod.repo >
/etc/yum.repos.d/mssql-release.repo



exit

sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts

sudo ACCEPT_EULA=Y yum install msodbcsql17





My /etc/odbc.ini looks like this

[testmssql]

Driver=ODBC Driver 17 for SQL Server

Server=server_foo

User=test

Password=password

Database=mydb

Port=1433



/etc/odbcinst.ini

[ODBC Driver 17 for SQL Server]

Description=Microsoft ODBC Driver 17 for SQL Server

Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1

UsageCount=1



From Oracle I create a database link as

create database link testmssql connect to test identified by password using
‘testmssql’;





I am very confused how to troubleshoot this.


Any help or if you have your own notes would be helpful.
Jeff Chirco
2018-07-30 19:36:11 UTC
Permalink
Yep I have all that. And I duplicated what you have except my file is name
"odbc.ini" and in a different location. I don't have it named ".odbc.ini"
We are doing this and have it working to both SQL Server and MariaDB.
The entries in .odbc.ini (location specified in $ORACLE_HOME/hs/admin/initDBNAME.ora)
[DBNAME]
Driver = ODBC Driver 17 for SQL Server
Trace = No
Server = 999.99.99.99,1433
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.1.so.0.1
Threading = 1
UsageCount = 1
Did you create an init.ora file for the instance in
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = DBNAME
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_NLS_NCHAR=UCS2
HS_TRANSACTION_MODEL=READ_ONLY
#
# ODBC specific environment variables
#
set ODBCINI=/home/oracle/.odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services
Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
*CONFIDENTIALITY NOTE*: The information transmitted, including
attachments, is intended only for the person(s) or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any
action in reliance upon this information by persons or entities other than
the intended recipient is prohibited. If you received this in error, please
contact the sender and destroy any copies of this information.
freelists.org] *On Behalf Of *Jeff Chirco
*Sent:* Monday, July 30, 2018 12:51 PM
*To:* oracle-l-freelist
*Subject:* odbc db link to MSSQL
Anybody have success with getting ODBC driver for SQL Server installed on
Oracle Linux 7 and getting a database link to work from 12.2
When I run a select over the db link I get this error.
ORA-28500: connection from ORACLE to a non-Oracle system returned this
ORA-02063: preceding line from TESTMSSQL
I didn't try the easysoft driver cause didn't want to have to buy a driver
for this. But if it is needed I will explore that option.
I installed unixODBC 2.3.6 from http://www.unixodbc.org
# remove any existing unixODBC drivers - be very careful with 'sudo rm'!
sudo rm /usr/lib64/libodbc*
# install the unixODBC driver
# note, adding "--enable-stats=no" here is not specified by Microsoft
export CPPFLAGS="-DSIZEOF_LONG_INT=8"
./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc
--enable-gui=no --enable-drivers=no --enable-iconv
--with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --enable-stats=no
1> configure_std.log 2> configure_err.log
make 1> make_std.log 2> make_err.log
sudo make install 1> makeinstall_std.log 2> makeinstall_err.log
# the Microsoft driver expects unixODBC to be here /usr/lib64/libodbc.so.1,
# so add soft links to the '.so.2' files
cd /usr/lib64
sudo ln -s libodbccr.so.2 libodbccr.so.1
sudo ln -s libodbcinst.so.2 libodbcinst.so.1
sudo ln -s libodbc.so.2 libodbc.so.1
Then I downloaded Microsoft ODBC Driver 17 from
https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/
installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017
sudo su
#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version
#RedHat Enterprise Server 7
curl https://packages.microsoft.com/config/rhel/7/prod.repo >
/etc/yum.repos.d/mssql-release.repo
exit
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
sudo ACCEPT_EULA=Y yum install msodbcsql17
My /etc/odbc.ini looks like this
[testmssql]
Driver=ODBC Driver 17 for SQL Server
Server=server_foo
User=test
Password=password
Database=mydb
Port=1433
/etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
UsageCount=1
From Oracle I create a database link as
create database link testmssql connect to test identified by password
using ‘testmssql’;
I am very confused how to troubleshoot this.
Any help or if you have your own notes would be helpful.
Jeff Chirco
2018-07-30 19:47:50 UTC
Permalink
Yeah I have entries in the the tnsnames and listener. What do your
listener.ora file look like?
Thank you for your help.
It doesn’t matter where it is and what it’s called since the path to it is
in the initDBNAME.ora file.
Of course you have an entry in the tnsnames.ora file and the port is open
between the servers.
Can you tnsping the SQL Server?
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services
Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
*CONFIDENTIALITY NOTE*: The information transmitted, including
attachments, is intended only for the person(s) or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any
action in reliance upon this information by persons or entities other than
the intended recipient is prohibited. If you received this in error, please
contact the sender and destroy any copies of this information.
*Sent:* Monday, July 30, 2018 3:31 PM
*To:* Scott Canaan
*Subject:* Re: odbc db link to MSSQL
Yep I have all that. And I duplicated what you have except my file is name
"odbc.ini" and in a different location. I don't have it named ".odbc.ini"
We are doing this and have it working to both SQL Server and MariaDB.
The entries in .odbc.ini (location specified in $ORACLE_HOME/hs/admin/initDBNAME.ora)
[DBNAME]
Driver = ODBC Driver 17 for SQL Server
Trace = No
Server = 999.99.99.99,1433
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.1.so.0.1
Threading = 1
UsageCount = 1
Did you create an init.ora file for the instance in
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = DBNAME
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_NLS_NCHAR=UCS2
HS_TRANSACTION_MODEL=READ_ONLY
#
# ODBC specific environment variables
#
set ODBCINI=/home/oracle/.odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services
Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
*CONFIDENTIALITY NOTE*: The information transmitted, including
attachments, is intended only for the person(s) or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any
action in reliance upon this information by persons or entities other than
the intended recipient is prohibited. If you received this in error, please
contact the sender and destroy any copies of this information.
freelists.org] *On Behalf Of *Jeff Chirco
*Sent:* Monday, July 30, 2018 12:51 PM
*To:* oracle-l-freelist
*Subject:* odbc db link to MSSQL
Anybody have success with getting ODBC driver for SQL Server installed on
Oracle Linux 7 and getting a database link to work from 12.2
When I run a select over the db link I get this error.
ORA-28500: connection from ORACLE to a non-Oracle system returned this
ORA-02063: preceding line from TESTMSSQL
I didn't try the easysoft driver cause didn't want to have to buy a driver
for this. But if it is needed I will explore that option.
I installed unixODBC 2.3.6 from http://www.unixodbc.org
# remove any existing unixODBC drivers - be very careful with 'sudo rm'!
sudo rm /usr/lib64/libodbc*
# install the unixODBC driver
# note, adding "--enable-stats=no" here is not specified by Microsoft
export CPPFLAGS="-DSIZEOF_LONG_INT=8"
./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc
--enable-gui=no --enable-drivers=no --enable-iconv
--with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --enable-stats=no
1> configure_std.log 2> configure_err.log
make 1> make_std.log 2> make_err.log
sudo make install 1> makeinstall_std.log 2> makeinstall_err.log
# the Microsoft driver expects unixODBC to be here /usr/lib64/libodbc.so.1,
# so add soft links to the '.so.2' files
cd /usr/lib64
sudo ln -s libodbccr.so.2 libodbccr.so.1
sudo ln -s libodbcinst.so.2 libodbcinst.so.1
sudo ln -s libodbc.so.2 libodbc.so.1
Then I downloaded Microsoft ODBC Driver 17 from
https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/
installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017
sudo su
#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version
#RedHat Enterprise Server 7
curl https://packages.microsoft.com/config/rhel/7/prod.repo >
/etc/yum.repos.d/mssql-release.repo
exit
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
sudo ACCEPT_EULA=Y yum install msodbcsql17
My /etc/odbc.ini looks like this
[testmssql]
Driver=ODBC Driver 17 for SQL Server
Server=server_foo
User=test
Password=password
Database=mydb
Port=1433
/etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
UsageCount=1
From Oracle I create a database link as
create database link testmssql connect to test identified by password
using ‘testmssql’;
I am very confused how to troubleshoot this.
Any help or if you have your own notes would be helpful.
Jeff Chirco
2018-07-30 21:50:09 UTC
Permalink
Darn still having trouble. I can connect from the Linux OS using isql
[odbc_dbname] [user] [password] - v But just can't query over dblink from
Oracle.
INBOUND_CONNECT_TIMEOUT_LISTENER = 0
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dbname2.rit.edu)
(SID_NAME = DDNAME2)
(ORACLE_HOME = /oracle/app/product/12.1.0.2)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/product/12.1.0.2)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:/oracle/data/scripts/lib/syslog.so")
)
#
# SQL Server
#
(SID_DESC =
(GLOBAL_DBNAME = dbname.ad.rit.edu)
(PROGRAM = dg4odbc)
(SID_NAME = DBNAME)
(ORACLE_HOME = /oracle/app/product/12.1.0.2)
(ENV = "LD_LIBRARY_PATH=/usr/lib64:/oracle/app/product/12.1.0.2/
lib:/oracle/app/product/12.1.0.2/hs")
)
)
DYNAMIC_REGISTRATION_LISTENER = OFF
ADMIN_RESTRICTIONS_LISTENER = ON
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 999.99.99.999)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY=EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 999.99.99.999)(PORT = 1524))
)
)
)
ADR_BASE_LISTENER = /oracle/app
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER = OFF
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services
Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
*CONFIDENTIALITY NOTE*: The information transmitted, including
attachments, is intended only for the person(s) or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any
action in reliance upon this information by persons or entities other than
the intended recipient is prohibited. If you received this in error, please
contact the sender and destroy any copies of this information.
*Sent:* Monday, July 30, 2018 3:48 PM
*To:* Scott Canaan; oracle-l-freelist
*Subject:* Re: odbc db link to MSSQL
Yeah I have entries in the the tnsnames and listener. What do your
listener.ora file look like?
Thank you for your help.
It doesn’t matter where it is and what it’s called since the path to it is
in the initDBNAME.ora file.
Of course you have an entry in the tnsnames.ora file and the port is open
between the servers.
Can you tnsping the SQL Server?
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services
Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
*CONFIDENTIALITY NOTE*: The information transmitted, including
attachments, is intended only for the person(s) or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any
action in reliance upon this information by persons or entities other than
the intended recipient is prohibited. If you received this in error, please
contact the sender and destroy any copies of this information.
*Sent:* Monday, July 30, 2018 3:31 PM
*To:* Scott Canaan
*Subject:* Re: odbc db link to MSSQL
Yep I have all that. And I duplicated what you have except my file is name
"odbc.ini" and in a different location. I don't have it named ".odbc.ini"
We are doing this and have it working to both SQL Server and MariaDB.
The entries in .odbc.ini (location specified in $ORACLE_HOME/hs/admin/initDBNAME.ora)
[DBNAME]
Driver = ODBC Driver 17 for SQL Server
Trace = No
Server = 999.99.99.99,1433
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.1.so.0.1
Threading = 1
UsageCount = 1
Did you create an init.ora file for the instance in
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = DBNAME
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_NLS_NCHAR=UCS2
HS_TRANSACTION_MODEL=READ_ONLY
#
# ODBC specific environment variables
#
set ODBCINI=/home/oracle/.odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services
Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
*CONFIDENTIALITY NOTE*: The information transmitted, including
attachments, is intended only for the person(s) or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any
action in reliance upon this information by persons or entities other than
the intended recipient is prohibited. If you received this in error, please
contact the sender and destroy any copies of this information.
freelists.org] *On Behalf Of *Jeff Chirco
*Sent:* Monday, July 30, 2018 12:51 PM
*To:* oracle-l-freelist
*Subject:* odbc db link to MSSQL
Anybody have success with getting ODBC driver for SQL Server installed on
Oracle Linux 7 and getting a database link to work from 12.2
When I run a select over the db link I get this error.
ORA-28500: connection from ORACLE to a non-Oracle system returned this
ORA-02063: preceding line from TESTMSSQL
I didn't try the easysoft driver cause didn't want to have to buy a driver
for this. But if it is needed I will explore that option.
I installed unixODBC 2.3.6 from http://www.unixodbc.org
# remove any existing unixODBC drivers - be very careful with 'sudo rm'!
sudo rm /usr/lib64/libodbc*
# install the unixODBC driver
# note, adding "--enable-stats=no" here is not specified by Microsoft
export CPPFLAGS="-DSIZEOF_LONG_INT=8"
./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc
--enable-gui=no --enable-drivers=no --enable-iconv
--with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --enable-stats=no
1> configure_std.log 2> configure_err.log
make 1> make_std.log 2> make_err.log
sudo make install 1> makeinstall_std.log 2> makeinstall_err.log
# the Microsoft driver expects unixODBC to be here /usr/lib64/libodbc.so.1,
# so add soft links to the '.so.2' files
cd /usr/lib64
sudo ln -s libodbccr.so.2 libodbccr.so.1
sudo ln -s libodbcinst.so.2 libodbcinst.so.1
sudo ln -s libodbc.so.2 libodbc.so.1
Then I downloaded Microsoft ODBC Driver 17 from
https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/
installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017
sudo su
#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version
#RedHat Enterprise Server 7
curl https://packages.microsoft.com/config/rhel/7/prod.repo >
/etc/yum.repos.d/mssql-release.repo
exit
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
sudo ACCEPT_EULA=Y yum install msodbcsql17
My /etc/odbc.ini looks like this
[testmssql]
Driver=ODBC Driver 17 for SQL Server
Server=server_foo
User=test
Password=password
Database=mydb
Port=1433
/etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
UsageCount=1
From Oracle I create a database link as
create database link testmssql connect to test identified by password
using ‘testmssql’;
I am very confused how to troubleshoot this.
Any help or if you have your own notes would be helpful.
Jeff Chirco
2018-07-30 21:58:31 UTC
Permalink
Ok success! Thanks for your help. I think I had a mix up with some
different tests I was trying.
Post by Jeff Chirco
Darn still having trouble. I can connect from the Linux OS using isql
[odbc_dbname] [user] [password] - v But just can't query over dblink from
Oracle.
INBOUND_CONNECT_TIMEOUT_LISTENER = 0
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dbname2.rit.edu)
(SID_NAME = DDNAME2)
(ORACLE_HOME = /oracle/app/product/12.1.0.2)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/product/12.1.0.2)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:/oracle/data/scripts/lib/syslog.so")
)
#
# SQL Server
#
(SID_DESC =
(GLOBAL_DBNAME = dbname.ad.rit.edu)
(PROGRAM = dg4odbc)
(SID_NAME = DBNAME)
(ORACLE_HOME = /oracle/app/product/12.1.0.2)
(ENV = "LD_LIBRARY_PATH=/usr/lib64:/oracle/app/product/12.1.0.2/lib
:/oracle/app/product/12.1.0.2/hs")
)
)
DYNAMIC_REGISTRATION_LISTENER = OFF
ADMIN_RESTRICTIONS_LISTENER = ON
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 999.99.99.999)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY=EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 999.99.99.999)(PORT = 1524))
)
)
)
ADR_BASE_LISTENER = /oracle/app
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER = OFF
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services
Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
*CONFIDENTIALITY NOTE*: The information transmitted, including
attachments, is intended only for the person(s) or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any
action in reliance upon this information by persons or entities other than
the intended recipient is prohibited. If you received this in error, please
contact the sender and destroy any copies of this information.
*Sent:* Monday, July 30, 2018 3:48 PM
*To:* Scott Canaan; oracle-l-freelist
*Subject:* Re: odbc db link to MSSQL
Yeah I have entries in the the tnsnames and listener. What do your
listener.ora file look like?
Thank you for your help.
It doesn’t matter where it is and what it’s called since the path to it
is in the initDBNAME.ora file.
Of course you have an entry in the tnsnames.ora file and the port is open
between the servers.
Can you tnsping the SQL Server?
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services
Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
*CONFIDENTIALITY NOTE*: The information transmitted, including
attachments, is intended only for the person(s) or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any
action in reliance upon this information by persons or entities other than
the intended recipient is prohibited. If you received this in error, please
contact the sender and destroy any copies of this information.
*Sent:* Monday, July 30, 2018 3:31 PM
*To:* Scott Canaan
*Subject:* Re: odbc db link to MSSQL
Yep I have all that. And I duplicated what you have except my file is
name "odbc.ini" and in a different location. I don't have it named
".odbc.ini"
We are doing this and have it working to both SQL Server and MariaDB.
The entries in .odbc.ini (location specified in
[DBNAME]
Driver = ODBC Driver 17 for SQL Server
Trace = No
Server = 999.99.99.99,1433
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.1.so.0.1
Threading = 1
UsageCount = 1
Did you create an init.ora file for the instance in
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = DBNAME
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_NLS_NCHAR=UCS2
HS_TRANSACTION_MODEL=READ_ONLY
#
# ODBC specific environment variables
#
set ODBCINI=/home/oracle/.odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services
Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
*CONFIDENTIALITY NOTE*: The information transmitted, including
attachments, is intended only for the person(s) or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any
action in reliance upon this information by persons or entities other than
the intended recipient is prohibited. If you received this in error, please
contact the sender and destroy any copies of this information.
sts.org] *On Behalf Of *Jeff Chirco
*Sent:* Monday, July 30, 2018 12:51 PM
*To:* oracle-l-freelist
*Subject:* odbc db link to MSSQL
Anybody have success with getting ODBC driver for SQL Server installed on
Oracle Linux 7 and getting a database link to work from 12.2
When I run a select over the db link I get this error.
ORA-28500: connection from ORACLE to a non-Oracle system returned this
ORA-02063: preceding line from TESTMSSQL
I didn't try the easysoft driver cause didn't want to have to buy a
driver for this. But if it is needed I will explore that option.
I installed unixODBC 2.3.6 from http://www.unixodbc.org
# remove any existing unixODBC drivers - be very careful with 'sudo rm'!
sudo rm /usr/lib64/libodbc*
# install the unixODBC driver
# note, adding "--enable-stats=no" here is not specified by Microsoft
export CPPFLAGS="-DSIZEOF_LONG_INT=8"
./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc
--enable-gui=no --enable-drivers=no --enable-iconv
--with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --enable-stats=no
1> configure_std.log 2> configure_err.log
make 1> make_std.log 2> make_err.log
sudo make install 1> makeinstall_std.log 2> makeinstall_err.log
# the Microsoft driver expects unixODBC to be here
/usr/lib64/libodbc.so.1,
# so add soft links to the '.so.2' files
cd /usr/lib64
sudo ln -s libodbccr.so.2 libodbccr.so.1
sudo ln -s libodbcinst.so.2 libodbcinst.so.1
sudo ln -s libodbc.so.2 libodbc.so.1
Then I downloaded Microsoft ODBC Driver 17 from
https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/
installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017
sudo su
#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version
#RedHat Enterprise Server 7
curl https://packages.microsoft.com/config/rhel/7/prod.repo >
/etc/yum.repos.d/mssql-release.repo
exit
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
sudo ACCEPT_EULA=Y yum install msodbcsql17
My /etc/odbc.ini looks like this
[testmssql]
Driver=ODBC Driver 17 for SQL Server
Server=server_foo
User=test
Password=password
Database=mydb
Port=1433
/etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
UsageCount=1
From Oracle I create a database link as
create database link testmssql connect to test identified by password
using ‘testmssql’;
I am very confused how to troubleshoot this.
Any help or if you have your own notes would be helpful.
Loading...