Re: odbc db link to MSSQL

From: Jeff Chirco <backseatdba_at_gmail.com>
Date: Mon, 30 Jul 2018 14:50:09 -0700
Message-ID: <CAKsxbLoz2DKrYdNp70uLuqt_JtdKTBWj2LwPD53sx13Km3uTEA_at_mail.gmail.com>



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_at_rit.edu> 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_at_rit.edu | 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_at_gmail.com]
> *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_at_rit.edu> 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_at_rit.edu | 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_at_gmail.com]
> *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_at_rit.edu> 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_at_rit.edu | 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_at_freelists.org [mailto:oracle-l-bounce_at_
> 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.
>
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jul 30 2018 - 23:50:09 CEST

Original text of this message