스터디그룹 > ORACLE,MYSQL DB관련 > 오라클에서 MS SQL Server 2000 접속방법입니다.

ODBC를 이용한 방법을 해보세요.

PURPOSE
-------

This article documents setting up and troubleshooting Heterogeneous Services,
also referred to as "Generic Connectivity", to Microsoft SQL Server
using ODBC on Windows NT.

SCOPE & APPLICATION
-------------------

This bulletin describes a setup of Oracle 8.1.6 running on Windows
NT 4.0 to access a MS SQL Server database using the Microsoft Data
Access Components (MDAC)SDK ODBC driver.

The bulletin assumes the Microsoft SQL Server system is already established on
a Windows NT Network.

-------------------------------------------------------------------
1) Install the Microsoft Data Access Components (MDAC)SDK ODBC driver. This can
   be downloaded from http://www.microsoft.com/data.

2) Install the Oracle Server.

   A "Typical Installation" will include "Generic Connectivity using ODBC".
   If performing a "Custom Installation", do not omit this component.
   Directory "ORACLE_HOME/HS" is created during the installation which includes
   sample files.

3) Ensure CATHS has been run.

   Oracle Generic Connectivity feature requires the data dictionary objects
   created by script "ORACLE_HOME\RDBMS\ADMIN\CATHS.SQL".

   To test whether CATHS has run, do a "describe" of one of the HS objects. For
   example, connect to the Oracle server as schema "SYSTEM" and do
   "DESCRIBE SYS.HS_FDS_CLASS". If it does not exist, connect as SYS (or
   internal) and run the CATHS script.

4) Configure an ODBC System Data Source.

   From the Control panel select "Data Sources (ODBC)". Click the "System DSN"
   tab. Click the "Add" button. Select the "SQL Server" driver. In the
   configuration panel, key a name and description of your choice. Remember the
   name, it is required later - on my system I called it "sstest". Select a SQL
   Server from the drop-down menu. Choose your preference for
   authentication. I chose "SQL Server authentication" and specified userid
   "SA" (you can guess the password). Click "Next". I didn't change any
   further options on the next two panels. Click "Finish", you get a button to
   "test data source", which worked for me, then clicked "ok" twice to finish.

5) Configure tnsnames.ora. This file is in ORACLE_HOME\NETWORK\ADMIN.
   Add an entry, e.g.:

   hsodbc.uk.oracle.com =
(DESCRIPTION=
(ADDRESS=
   (PROTOCOL=tcp)
   (HOST=<HOSTNAME>)    |<- adjust hostname for your system
   (PORT=1521)    |<- adjust port if not using 1521
)
(CONNECT_DATA=
   (SID=hsodbc)
)
(HS=)
)

Note, a sample file is located in ORACLE_HOME\HS\ADMIN.

6) Configure listener.ora. This file is also in ORACLE_HOME\NETWORK\ADMIN.
   Add the following line to the SID_List of the listener.ora

(SID_DESC=
   (SID_NAME=hsodbc)
   (ORACLE_HOME=d:\oracle\ora81) |<- adjust Oracle_Home directory
   (PROGRAM=hsodbc)
   )

   Note, a sample file is located in ORACLE_HOME\HS\ADMIN.

7) Restart the Oracle listener:
   In an MSDOS window, issue commands "LSNRCTL STOP" then LSNRCTL START".
   Do "LSNRCTL STATUS" to check there is a service handler for your hsodbc
   entry.

8) Adjust the configuration file of the gateway. It is located in
   ORACLE_HOME\HS\ADMIN. The name depends on the SID you use for the
   Heterogeneous Service.
   In my example the listener SID is "hsodbc", thus the configuration file
   is inithsodbc.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 = sstest |<- ODBC Data Source Name configured in step 4
   HS_FDS_TRACE_LEVEL = 0 |<- trace levels are from 0 to 4 (4 is maximum)
   HS_FDS_TRACE_FILE_NAME = sstest.trc |<- adjust as required

   The trace file specification can be commented out if not required.
   Trace files are generated with default names in the ORACLE_HOME/HS/TRACE
   directory if trace level is not zero.

9) In an SQL*Plus session, create a database link. E.g.:

   create database link hsodbc using 'hsodbc';

10) Test the connection. E.g.:

select * from authors@hsodbc; ;

ODBC PROBLEM DETERMINATION
--------------------------

If problems are encountered, (e.g. ORA-9100 errors), please check the ODBC
configuration and obtain the following information prior to reporting the problem
to Oracle Support Services:

1. You should be using an MS SQL ODBC Driver.
a. What is the DSN (Data Source Name)? This can be determined by
   clicking on 'Control Panel' then click on 'ODBC' then click on
   the 'System DSN' tab.

b. What is the SQL Driver Version? Click on the '32 bit Administrator'
   icon in the Oracle group, Click on the 'Driver' button then Click on
   the SQL Server driver and choose 'About'.

c. Obtain an ODBC trace:
Run REGEDT32, click on 'hkey_local_machine', choose
software, odbc, odbc.ini. Change the value of the
TRACE parameter to 1 and provide a valid trace file
name in the TRACEFILE parameter.

장시영(sironge)님이 2004-05-24 12:45:09에 작성한 댓글입니다.답변 감사 합니다.

그런데 위 방법의 해결책을 아시는 분이 계시면 알려 주셨으면 합니다.

어떻게 해야 하는건지 궁금해서리...^^;;;