Home » RDBMS Server » Networking and Gateways » Problem in connecting Sql server2000 from Oracle10g using oracle gateway (using Db links)  () 1 Vote
Problem in connecting Sql server2000 from Oracle10g using oracle gateway (using Db links) [message #195691] Sun, 01 October 2006 06:51 Go to next message
dbcop
Messages: 37
Registered: September 2006
Location: india
Member
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

****************GATEWAY FILES WHICH WERE MODIFIED***************

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@lnk2;
select * from sa.employees@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

1. 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.
Re: Problem in connecting Sql server2000 from Oracle10g using oracle gateway (using Db links) [message #195840 is a reply to message #195691] Mon, 02 October 2006 14:26 Go to previous message
dbcop
Messages: 37
Registered: September 2006
Location: india
Member
Hi Guys please find some time to help me....

Thanks
Previous Topic: connectivity through internet without any web server
Next Topic: How to select data throw two dblinks ?
Goto Forum:
  


Current Time: Tue Dec 03 05:16:33 CST 2024