Heterogeneus services connecting to SQL Server 2000

[vac]

Hello, I have two machines one with Oracle and other with SQL Server 2000 and I need query SQL Server tables from Oracle database.

I use the article http://www.dbasupport.com/oracle/ora...ection01.shtml and it was ok if both databases are in the same machine but when are in different and I tried to start listenermysqlserverdsn
this error appears:

TNS-12545: Connect failed because target host or object does not exist

Question 1: SQL Server machine need Oracle client installed?

Question 2: The solution is the transparent gateway?

Any idea?

Thanks

[skhanal]

Can you connect to the SQL Server machine from the Oracle machine using PING or SQL Client?

[vac]

Ping ok, and I can make odbc connection from Oracle server to Sql Server.

[Ach]

Answer 1: Yes it needs Oracle Client.They connect on Net services so I think it should be a client installation.
Answer 2: Not necessarily,Generic connectivity should perform fine on this situation.Do you have LOCAL or TWO_TASK variables set? Do you qualify target host name?

Note: I am new in this forum.I see more than 200 person have seen this post and only 1 answer is here
Is here just a place to watch posts?No it can't be...

[oraclejo]

You have a problem with the defintion of SQL NET most probably with tnsnames.ora. of you switch to Oracle Gateway , you will fact the same problem, because tnsnames.ora is used to define the connect string to the other database.

Most common errors when using tnsnames.ora


1- you have not configured your tnsnames.ora file at all

2- you have but did not provide the correct information for host, port, and SID

3- you have not configured your Listener.ora

4- you have not configured it correctly (make sure that HOST is actually the same host where it is installed and not where you SQL database is installed)
5- you have more than one client installation or Oracle installation at different directories and you request is accessing the wrong one.

anyway, this is a simple situation and faces many ppl who have little experience with it,, need to grasp the idea of SQL*NET and then you make no mistakes.

Anyway, i have a web site with some tips where i details HS connectivity with EXCEL sheet (the excel sheet simulates the hetro part of the connectivity), I have also test similar connectivity with DB2 on IBM AS/400 machine and it worked fine.

Ammar Sajdi

Oracle consultant

www.e-ammar.com

hope this helps