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.No rthwind
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.



Thanks and Regards

"Nick" <mitra.kausik@gmail.com> schreef in bericht
news:1159868106.158541.93690@m73g2000cwd.googlegro ups.com...

From http://www.datadirect.com/developer...neous/index.ssp
I found the next:
ORA-28500: connection from ORACLE to a non-Oracle system returned this
message: [Transparent gateway for ODBC]DRV_InitTdp: [DATADIRECT][ODBC SQL
Server Driver][SQL Server] Login failed
(SQL State: 28000; SQL Code: 4002)
ORA-02063: preceding 3 lines from HSTEST
Cause: The Oracle database link created for the foreign datasource has
either no credentials or incorrect credentials.
Action: Recreate the Oracle database link with the proper username and
password. Also, username and password must be in double quotes.
Example: create database link ODBC connect to "sa" identified by "pencil"
using 'hsodbc'.

Looks like you forgot the double quotes for the user, and quotes fir the
using clause... and: watch out for case-sensitivity.

Using other names in your connections than 'hsodbc' is somewhat dangerous: a
'search/replace' on hsodbc with e.g. tg4msql may create errors. As I recall,
(but it's a long time ago I used HS) the line 'program=hsodbc' in
listener.ora should not be changed in tg4msql.

So it should look like

TNSNAMES.ORAtg4msql=
(description=
(address=(protocol=tcp)(host=hostname)(port=1521))
(connect_data=(sid=tg4msql)) Needs to match the sid in listener.ora.
(hs=ok) hs clause goes in the description.
)LISTENER.ORAlistener =
(description_list =
(description =
(address_list =
(address = (protocol = tcp)(host = stlap02174.TechMahindra.com)(port
= 1521))
)
)

sid_list_listener=
(sid_list=
(sid_desc=
(sid_name=tg4msql) Match the sid in
tnsnames.ora.
(oracle_home=<your oracle home> ) Appropriate $ORACLE_HOME
(program= hsodbc) Agent Executable
)
)To avoid errors, it's best to call everything hsodbc ;-) I only got it
working that way (but maybe my error was NOT renaming the program=
clause...)

If I had to guess, since the connection seems to go to the right database,
I'd say the double quotes are the problem....

Robbert

Attached Images

 

Thank you Robbert for your reply but i checked for case sensitivity
Tried with double quotes as well
create database link lnk1 connect to "sa" identified by "sa" using
'tg4msql';
Regarding listener configuration as am using transparent gateways for
sql server so the

program=tg4msql should be there right instead of (program= hsodbc)
Agent Executable
Still the problem remains... I don't find the gateway instance service
under services panel it should have been there right?
Awaiting your reply....

Thanks
Nick

Robbert van der Hoorn wrote:

"Nick" <mitra.kausik@gmail.com> schreef in bericht
news:1159908563.976040.148370@m73g2000cwd.googlegr oups.com...

You're right, my mistake.


I'm not shure about that. You can check your listener with lsnrctl
(lstnrctl> services)


And here it is!


Some suggestions (don't know if they will work
- remove SQLNET.AUTHENTICATION_SERVICES= (NTS) (Some vague memory tells me
this should not be used with HS) by changing it to
SQLNET.AUTHENTICATION_SERVICES= (NONE)
- remove the .world from your tnsnames entry for tg4msql in both THSNAMES
files and test
- if no help, put it back, put it in BOTH files and make a link to
tg4msql.world, try again
- remove the sa- prefixes from your select statement
- try to do select * from dual@tg4msql
- remove the SERVER=DEDICATED from TNSNAMES at the server

If it still does not work, I'm out of options, except that you might want to
switch to HS with ODBC.

Robbert