| 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
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_at_freelists.org]On Behalf Of Smith, Ron L.
Sent: Wednesday, 5 May 2004 1:01 AM
Thank you very much!
-----Original Message-----
[mailto:oracle-l-bounce_at_freelists.org] 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)
(CONNECT_DATA =3D3D (SID =3D3D SQLSERVERSID))
(hs=3D3Dok)
)
-- 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 - 18:09:36 CDT
 |
 |