Problem in connecting Sql server2000 from Oracle10g using oracle gateway (using Db links)
Date: 3 Oct 2006 02:35:06 -0700
Message-ID: <1159868106.158541.93690_at_m73g2000cwd.googlegroups.com>
Hi Gurus ,
My requirement is to connect sql server2000 database from oracle10g
using Oracle Transparent Gateways.
Both the databases arrunning on the same server.
Environment : WIndows XP.
I am writing down the steps I did and the files I modified to do the same.
1.Installed Oracle Transparent Gateways which came in with Oracle10g Database server CD. With intallation type as CUSTOM.
After installation it did not create any Gateway instance but files were available at Oracle_home\TG4MSQl\listner/tnsnames etc.
Now I am going to give u the files I changed
1.The gateway files are located in E:\oracle\tg4msql\admin
After Modification
Inittg4msql file looks like
HS_FDS_CONNECT_INFO=stlap02174.TechMahindra.com.Northwind HS_FDS_TRACE_LEVEL=DEBUG HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER ================================================================
2.Gateway listener file looks like
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = extproc)
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle)
)
(SID_DESC =
(PROGRAM = tg4msql)
(SID_NAME = tg4msql)
(ORACLE_HOME = E:\Oracle)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stlap02174.TechMahindra.com)(PORT =
1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
3.Gateway tnsnames.ora file looks like
TG4MSQL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stlap02174.TechMahindra.com)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tg4msql)
)
(HS = OK)
)
*****Database Server Files which were Modified*********
Database listener.ora file in E:\oracle\network\admin looks like
1.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = extproc)
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle)
)
(SID_DESC =
(PROGRAM = tg4msql)
(SID_NAME = tg4msql)
(ORACLE_HOME = E:\Oracle)
)
(SID_DESC =
(GLOBAL_DBNAME = sudan)
(ORACLE_HOME = e:\oracle)
(SID_NAME = sudan)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stlap02174.TechMahindra.com)(PORT =
1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
2.Database tnsnames.ora file in the same directory apart from other entries has an entry like
TG4MSQL.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stlap02174.TechMahindra.com)(PORT =
1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tg4msql)
)
(HS = OK)
)
3.Database server sqlnet.ora looks like
NAMES.DEFAULT_DOMAIN = world
NAMES.DEFAULT_ZONE = world
SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, ONAMES, HOSTNAME) DEFAULT_SDU_SIZE = 8761
Thats all I did after which i created a Database link as follows
SQL> create database link lnk2 connect to sa identified by sa using 'tg4msql';
Database link created.
SQL> select * from sa.employees_at_lnk2; select * from sa.employees_at_lnk2
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Transparent gateway for MSSQL]DRV_InitTdp: errors.h (937): ; [Microsoft][ODBC
SQL Server Driver][SQL Server]Login failed for user 'SA'. (SQL State: 00000;
SQL Code: 18456)
ORA-02063: preceding 2 lines from LNK2
The above is the error I am getting.
I had a few questions in this regard
- I don't find any Gateway service being created in the services panel. What is the purpose of this inittg4msql.ora? Is there any utility to create gateway service in oracle10g as it used to be in 8 like
CMD> Set GTW_EXE=TGMSQL80.exe
CMD> GTWSRV80 sid -create | more
2. As Gateway installation and database server is on the same machine
with one Listener which reads from
oracle_home\network\admin\listener.ora
Then what is the function of listener.ora under
oracle_home\tg4msql\admin
Same with Tnsnames.ora file.
Please help me as I have been trying with this for many days and its going nowhere.
Thanks and Regards Received on Tue Oct 03 2006 - 11:35:06 CEST