RE: Connection between Oracle on Unix and SQL Server
- From: "Duret, Kathy" <kduret@xxxxxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 4 May 2004 09:00:55 -0500
First of all, if the application is very stable on sqlserver and you don't
have to take the sql server database down regularly! Our app on sqlserver
S**Ks and I am trying to get the developers to use a separate JBBC or OBDC
connection to the database instead.
Currently, we are using this datadirect driver (the instructions from them
are very good, $2,000 a processor was what they charged us.) but there are
alot of caveats/problems.
You really need to get the information you need from sqlserver and get out.
Don't keep any connections open! I have also seen were these connections
hang the sqlserver database (which in our case with the apps that sits on
top of it is very easy to do).
What happens it that if someone has an open connection to this and the sql
server database goes down,.... you get a lovely core dump for each
connection open from Oracle. And swap and memory use on Oracle goes way up
and almost hangs the database...
e.g. error
ORA-28500: connection from ORACLE to a non-Oracle system returned this
message:
[Generic Connectivity Using ODBC][S1000] [9013]General error in
nvITrans_Commit
- rc = -1.
After you get the heterogeneous connections working, make sure you bounce
the Oracle database ASAP. Also, I can't remember the details or find my
notes on it but you have to create the database link in Oracle with some
double quotes else you get another fun Oracle error, search Metalink it was
there I believe the error was 27600?
In your oracle home you will need a hs directory and under the admin
directory you need to have a separate initLINKNAME.ora.
e.g. initSQLSERVERSID.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=msss_sqlserversid
HS_FDS_TRACE_LEVEL=on
HS_FDS_TRACE_FILE_NAME=sqlserversid.trc
HS_FDS_SHAREABLE_NAME=/opt/app/datadirect/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/opt/app/datadirect/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
You also need to make additions to your tnsnames.ora and listener.ora
e.g. listener.ora entry
(SID_DESC =
(sid_name = SQLSERVERSID)
(ORACLE_HOME = /opt/app/oracle/product/8.1.7)
(program = hsodbc)
)
)
e.g. tnsnames.ora entry
SQLSERVERSID =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = yourhostname)(PORT = portnumber)
(CONNECT_DATA = (SID = SQLSERVERSID))
(hs=ok)
)
Have fun.
Kathy
-----Original Message-----
From: Michael McMullen [mailto:ganstadba@xxxxxxxxxxx]
Sent: Monday, May 03, 2004 2:29 PM
To: oracle-l@xxxxxxxxxxxxx
Cc: rlsmith@xxxxxxx
Subject: Re: Connection between Oracle on Unix and SQL Server
If you're using the gateway it's only available on microsoft. If you use HS
it can sit on unix but you need an odbc driver. There was an article on
metalink that I followed and I got it to work. I used an odbc driver from
www.datadirect.com
Mike
ganstadba@xxxxxxxxxxx
----- Original Message -----
From: "Smith, Ron L." <rlsmith@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, May 03, 2004 2:59 PM
Subject: Connection between Oracle on Unix and SQL Server
> I need to implement a connection between Oracle on Unix and SQL Server.
> I have tried both Transparent Gateway and Heterogeneous Services but I
> have had not luck with either. This is a low volume connection that
> will be used on several different servers to various applications. =20
>
> Does anyone know where I can get some fool proof (in this case idiot
> proof) instructions on how to set up this type of connection?
>
> Thanks!
> Ron Smith
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
This transmission contains information solely for intended recipient and may
be privileged, confidential and/or otherwise protect from disclosure. If
you are not the intended recipient, please contact the sender and delete all
copies of this transmission. This message and/or the materials contained
herein are not an offer to sell, or a solicitation of an offer to buy, any
securities or other instruments. The information has been obtained or
derived from sources believed by us to be reliable, but we do not represent
that it is accurate or complete. Any opinions or estimates contained in
this information constitute our judgment as of this date and are subject to
change without notice. Any information you share with us will be used in
the operation of our business, and we do not request and do not want any
material, nonpublic information. Absent an express prior written agreement,
we are not agreeing to treat any information confidentially and will use any
and all information and reserve the right to publish or disclose any
information you share with us.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
