| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Connection between Oracle on Unix and SQL Server
Thank you very much!
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Duret, Kathy
Sent: Tuesday, May 04, 2004 9:01 AM
To: 'oracle-l_at_freelists.org'
Subject: RE: Connection between Oracle on Unix and SQL Server
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.=20
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. =20
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... =20
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 =3D -1.=20
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?=20
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=3Dmsss_sqlserversid HS_FDS_TRACE_LEVEL=3Don HS_FDS_TRACE_FILE_NAME=3Dsqlserversid.trc HS_FDS_SHAREABLE_NAME=3D/opt/app/datadirect/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=3D/opt/app/datadirect/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=3D<value>
You also need to make additions to your tnsnames.ora and listener.ora
e.g. listener.ora entry=20
(SID_DESC =3D
(sid_name =3D SQLSERVERSID)
(ORACLE_HOME =3D /opt/app/oracle/product/8.1.7)
(program =3D hsodbc)
(CONNECT_DATA =3D (SID =3D SQLSERVERSID))
(hs=3Dok)
)
Have fun.
Kathy
-----Original Message-----
From: Michael McMullen [mailto:ganstadba_at_hotmail.com]
Sent: Monday, May 03, 2004 2:29 PM
To: oracle-l_at_freelists.org
Cc: rlsmith_at_kmg.com
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_at_hotmail.com
----- Original Message -----
From: "Smith, Ron L." <rlsmith_at_kmg.com>
To: <oracle-l_at_freelists.org>
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=20 > Server. I have tried both Transparent Gateway and Heterogeneous=20 > Services but I have had not luck with either. This is a low volume=20 > connection that will be used on several different servers to various=20 > applications. =3D20 > > 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_at_freelists.org put=20 > 'unsubscribe' in the subject line. > -- > Archives are at http://www.freelists.org/archives/oracle-l/ > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ----------------------------------------------------------------Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue May 04 2004 - 09:59:32 CDT
 |
 |