|
|
|
|
|||||||||||||||||||
|
PURPOSE ------- This article provides a step-by-step guide setting
up and troubleshooting TG4MSQL (Transparent Gateway for MS SQL Server) for
a Windows based platform. This note applies to TG4MSQL release
8.1.6.0.0 and 9.0.1.0.1 - both TG4MSQL versions use the MS SQL Server client
library - as well to release 9.2.0.1.1 which uses ODBC as connect method.
SCOPE & APPLICATION ------------------- This bulletin is a summary of the manual. How to setup TG4MSQL -------------------- 1) -For Oracle
TG4MSQL V9.0.1 or 9.2: With the
OUI (Oracle Universal Installer) install
TG4MSQL.
This product is part of the server
installation CDs. ( Start
OUI, choose a sep. Oracle_Home, select the Oracle database
for installation. Then choose custom install. A product
list pops up where you have to scroll down to ORACLE
TRANSPARENT GATEWAY; click on the '+' in front and a product list opens. Choose the gateways
you want to install) -For Oracle
TG4MSQL V816: There is a
separate CD containing Oracle Transparent Gateway for MS SQL
Server release 8.1.6. Insert this CD and install TG4MSQL according
to the screen messages. -BOTH: A
directory called TG4MSQL will be created and a file called
TG4MSQL.EXE is placed in the bin directory of the appropriate
Oracle_Home. 2) TG4MSQL needs data dictionary tables in the
Oracle database. To check
their existence, run a query on i.e.
SYS.HS_FDS_CLASS. If it
fails, run the caths.sql script located in ORACLE_HOME\RDBMS\ADMIN\ as user
sys or internal. 3) Make sure for gateway version 8.1.6 (8i) and
9.0.1.0.0 (9iR1) that the Microsoft client is installed on the
gateway machine (at least it is sufficient
that the MS SQL Server Client DLL (ntwdblib.dll ) is available in the
search path. Perhaps
copy the file into the bin directory of your Oracle_Home installation.
For
V9.2.0.1.2 (9iR2) gateways, the access method changed from the client library to
ODBC. So please make sure to have the MS SQL Server ODBC driver
installed on the gateway machine. If it is not available, download the latest
MDAC Microsoft Data Access Components) from the Microsoft Web page and
install it. This package contains a SQL Server ODBC driver. 4) Tnsnames.ora.
This file
is in ORACLE_HOME\NETWORK\ADMIN. Add the
following lines to the file:
tg4msql.de.oracle.com = |<- adjust default domain
depending on the sqlnet.ora settings
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)
(HOST=<HOSTNAME>)
|<- adjust hostname (PORT=1521)) |<- adjust port
(CONNECT_DATA=(SID=tg4msql))
(HS=OK)
|<- attention: HS is outside CONNECT_DATA ) Note, a
sample file is located in ORACLE_HOME\tg4msql\ADMIN. COMMON ERRORS: Make sure,
that there are 2 closing brackets after the SID; the HS keyword
is outside of the Connect Data block. Further
make sure that only the TNS Alias is at the first position of the line; all
other lines must start at least with one SPACE (blank); otherwise
it is identified as an alias and the configuration is not correct. 5) Listener.ora: This file
is in ORACLE_HOME\NETWORK\ADMIN as well. Add the
following line to the SID_List of the listener.ora and restart the listener
afterwards. ( After the restart a service handler for tg4msql should
exist).
(SID_DESC=
(SID_NAME=tg4msql)
(ORACLE_HOME=e:\ora92) |<-
adjust Oracle_Home directory
(PROGRAM=tg4msql) ) Note, a
sample file is located in ORACLE_HOME\tg4msql\ADMIN. COMMON ERRORS: After
configuring the listener, restart it from the command line (lsnrctl
-> stop, start) and check the service summary. It must contain 1 service
handler for the TG4MSQL SID. 6) Adjust the configuration file of the
gateway. It is located in
ORACLE_HOME\TG4MSQL\ADMIN. The name depends on the SID you use for
the gateway. In our
sample the listener SID is tg4msql and so the configuration file must be
named inittg4msql.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 = <servername>.<database> |<- Please specify the server hostname
and do not use IP
addresses. The dot is
the delimeter and we use
it to separate the
servername from the database
name.
HS_FDS_TRACE_LEVEL = 0
|<- trace levels are from off to debug REMARK: Usually the following key is used to verify
the connect options to the MS
SQL Server:
HKEY_LOCAL_MACHINE\ SOFTWARE\ Microsoft\ MSSQLServer\ Client\
ConnectTo This
information normally specifies the ALIAS, the real server name,
instances (if they
are available) and port numbers of the SQL Server. Further
details can be found in [NOTE:231997.1] TG4MSQL and MS SQL Server
Instances. 7) Testing the connectivity between Oracle database
and the SQL Server: - create a
database link within the Oracle database to the SQL Server:
create database link tg4msql connect to "sa"
identified by "<password of sa>" using 'tg4msql'; - select *
from all_catalog@tg4msql; Example
selecting a demo table of the MS Northwind database: select
"TerritoryID" from
"EmployeeTerritories"@<link name>; COMMON ERRORS: The
usernames, passwords, table names, views, columns ... are case sensitive. For
creating the database link, make sure you surround the username, password by double
quotes and write them as they are defined in the MS SQL Server. Another
common error is, that the TG4MSQL does not allow OS Authentication (Windows
Authentication) for the SQL Server. Only SQL Server authentication with a
username AND a password is supported. Make sure, that the SQL Server Security
option is checked to allow both authentication methods. 8) Useful SQLServer commands. sp_help -
gives help on all the commands
sp_addlogin - sets up a login user account sp_adduser
- sets up a userid to use an individual database use
{database} - changes the database which sql is being targetted against.
sp_droplogin - remove a user account
sp_dropuser - remove a user from a database. ###################################################################################
COMMON ERRORS and SOLUTIONS ###################################################################################
/********************************************************************/
ORA-28509: unable to establish a connection to
non-Oracle system ORA-02063: preceding line from TG4MSQL /********************************************************************/
cause: This
indicates a problem with the Oracle configuration files. Action: Make sure
the HOST parameter in the tnsnames.ora file is correct. Make sure
the PORT number is correct. Make sure
the SID name is correct in both the TNSNAMES.ORA and LISTENER.ORA /********************************************************************/
ORA-28500: connection from ORACLE to a non-Oracle
system returned this message: [Transparent gateway for MS SQL Server] The
environment variable <HS_FDS_CONNECT_INFO> is not set. ORA-02063: preceding 2 lines from TG4MSQL /********************************************************************/
cause: Incorrect
parameter settings in the HS init.ora file. Action: Check
HS_FDS_CONNECT_INFO in the TG4MSQL init.ora file. It might
be missing or TG4MSQL is not able to find the correct initialisation
file. Make sure
the HS init.ora file exists in the ORACLE_HOME\tg4msql\admin directory
and has the same name as the SID in the LISTENER.ORA. Example:
If SID=mssql in the listener.ora file, then the nit.ora file would be
named ORACLE_HOME\hs\admin\initmssql.ora /********************************************************************/
ORA-00942: table or view does not exist [Transparent gateway for ODBC]DRV_OpenTable:
[Mircosoft ][ODBC SQL Server Driver][SQL Server]Invalid object name '%table%'.
(SQL State: S0002; SQL Code: 208) ORA-02063: preceding 2 lines from TG4MSQL /********************************************************************/
cause: The
init.ora file speciffies the wrong MS SQL Server database. A second
cause could be, that MS SQL Server tables are case sensitive and thus
should be surrounded by double quotes. RELATED DOCUMENTS ----------------- |
|||||||||||||||||||||
