RE: Connection between Oracle on Unix and SQL Server
- From: "Reardon, Bruce (CALBBAY)" <Bruce.Reardon@xxxxxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 5 May 2004 09:10:37 +1000
Hi Ron,
I have also found that if the database you're connecting from has global =
names set to true, you will want to add the following:
# set this so don't get ora-02085 when connecting from a database with =
global_names=3Dtrue
HS_DB_DOMAIN=3DWORLD
HS_DB_NAME=3DACCESSL
where hs_db_name is the same as HS_FDS_CONNECT_INFO
and hs_db_domain is the same as for the DB you're connecting from.
and in tnsnames, I found it helpful to put in "localhost" as the name of =
the HOST.
HTH,
Bruce Reardon
NOTICE: This e-mail and any attachments are private and confidential and =
may contain legally privileged information. If you are not an =
authorised recipient, the copying or distribution of this e-mail and any =
attachments is prohibited and you must not read, print or act in =
reliance on this e-mail or attachments. This notice should not be =
removed.
-----Original Message-----
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Smith, Ron L.
Sent: Wednesday, 5 May 2004 1:01 AM
Thank you very much!
-----Original Message-----
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Duret, Kathy
Sent: Tuesday, May 04, 2004 9:01 AM
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.=3D20
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. =3D20
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... =3D20
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 =3D3D -1.=3D20
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?=3D20
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=3D3Dmsss_sqlserversid
HS_FDS_TRACE_LEVEL=3D3Don
HS_FDS_TRACE_FILE_NAME=3D3Dsqlserversid.trc
HS_FDS_SHAREABLE_NAME=3D3D/opt/app/datadirect/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=3D3D/opt/app/datadirect/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=3D3D<value>
You also need to make additions to your tnsnames.ora and listener.ora
e.g. listener.ora entry=3D20
(SID_DESC =3D3D
(sid_name =3D3D SQLSERVERSID)
(ORACLE_HOME =3D3D /opt/app/oracle/product/8.1.7)
(program =3D3D hsodbc)
)
)
e.g. tnsnames.ora entry
SQLSERVERSID =3D3D
(DESCRIPTION =3D3D
(ADDRESS =3D3D (PROTOCOL =3D3D TCP)(HOST =3D3D yourhostname)(PORT =
=3D3D =3Dportnumber)
(CONNECT_DATA =3D3D (SID =3D3D SQLSERVERSID))
(hs=3D3Dok)
)
----------------------------------------------------------------
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:
