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.
On Mon, Jul 30, 2018 at 12:51 PM, Scott Canaan <srcdco@xxxxxxx> wrote:
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
srcdco@xxxxxxx | c: (585) 339-8659
*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.
*From:* Jeff Chirco [mailto:backseatdba@xxxxxxxxx]
*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.
On Mon, Jul 30, 2018 at 12:40 PM, Scott Canaan <srcdco@xxxxxxx> wrote:
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
srcdco@xxxxxxx | c: (585) 339-8659
*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.
*From:* Jeff Chirco [mailto:backseatdba@xxxxxxxxx]
*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"
On Mon, Jul 30, 2018 at 10:02 AM, Scott Canaan <srcdco@xxxxxxx> wrote:
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)
look like:
[DBNAME]
Driver = ODBC Driver 17 for SQL Server
Trace = No
Server = 999.99.99.99,1433
The entry in /etc/odbcinst.ini is:
[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
$ORACLE_HOME/hs/admin? It should be named initDBNAME.ora and look like:
#
# 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
srcdco@xxxxxxx | c: (585) 339-8659
*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.
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@
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
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.
