Book Fixed font Go to End Doc ID: Note:115992.1 Subject: Generic Connectivity: ODBC to MS SQL Server Type: BULLETIN Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 26-JUL-2000 Last Revision Date: 11-APR-2001 @ ************************************************************* This article is being delivered in Draft form and may contain errors. Please use the MetaLink "Feedback" button to advise Oracle of any issues related to this article. ************************************************************* 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=) |<- 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. RELATED DOCUMENTS ----------------- [NOTE:109730.1]
不过可以问问oracle的技术支持
oracle好像有个软件叫“透明网关”能完这样的功能
不过即使实现了也是千疮百孔的，理论上可以，实际上一定有问题 。
Subject: Generic Connectivity: ODBC to MS SQL Server
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 26-JUL-2000
Last Revision Date: 11-APR-2001
@ *************************************************************
This article is being delivered in Draft form and may contain
errors. Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
************************************************************* 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.
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=) |<- 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. RELATED DOCUMENTS
----------------- [NOTE:109730.1]