Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Connection between Oracle on Unix and SQL Server

RE: Connection between Oracle on Unix and SQL Server

From: Reardon, Bruce (CALBBAY) <>
Date: Wed, 5 May 2004 09:10:37 +1000
Message-ID: <>

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 =
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.

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-----
[]On Behalf Of Smith, Ron L. Sent: Wednesday, 5 May 2004 1:01 AM
Thank you very much!
-----Original Message-----
[] 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


# 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_name =3D3D SQLSERVERSID)
      (ORACLE_HOME =3D3D /opt/app/oracle/product/8.1.7)
      (program =3D3D hsodbc)

e.g. tnsnames.ora entry
    (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:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue May 04 2004 - 18:09:36 CDT

Original text of this message