Linked Server Oracle to SQL Server7

[cdutta]

Hi everybody,

I have been trying to create a link between Oracle 8i (v 8.1.6) to SQL Server7. I want to pull data from Sql server into Oracle.
I was able create the link that works when both Sql Server and Oracle were residing on the same box. However when I try to create a link across a network (between different boxes)I do not have the same success.

I am running Oracle 8i 8.1.6 on Windows NT 4 sp 6.
SQL Server 7.

Right now I am following the steps:

1. Logged in Oracle as sys I ran cath.sql script.

2. I created a odbc connection using MS SQL Server ODBC driver called pubs that goes againg a pubs database in SQL Server runnig across a network. Name of server is pic-naim.

3. I modified the HS\initodbc.ora file
and renamed it initpubs.ora
#
# HS init parameters
#
HS_FDS_CONNECT_INFO =pubs
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = pubs_trace_file
I am not sure if the name of this file stays as initodbc.ora
or gets modified to initpubs.ora.


4.The entry in tnsnames.ora

hsodbc.1FBUSA.COM =
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS= (PROTOCOL=TCP))
(HOST = 192.1.5.3) <-this is the IP address of SQL Server
(PORT = 1433) <-Port used by TCP
)
)
(CONNECT_DATA=(SID=hsodbc)
)
(HS=OK)
)

I am not sure if I should use pic-naim (name of the server) or IP address and I am not sure about the use of 1433 as the port number( this is the port number that SQL Server has configured )

5. listener entry:

(SID_DESC=
(SID_NAME=hsodbc)
(ORACLE_HOME=C:\Oracle)
(PROGRAM=hsodbc)
)

)

6.I created a link as following :

create database link pubs using 'hsodbc';

when I try to query from the link

select * from titles@pubs;

ORA-12154: TNS:could not resolve service name

Please help...What am I doing wrong??? ANy suggestions will be greatly appreciated../
Thanks,
CD

[Prof]

I think that this is two problems
1. Link from MS SQL to Oracle.
2. Link from Oracle to MS SQL.

Link from MS SQL to Oracle is very simple question because you can create database link from MS SQL by ODBC. ( see Enterprise manager -> DTS )

You can create link from Oracle to MS SQL 2 way:
1. The best way Oracle Transparent Gateway.
2. Oracle Heteregenios Agent ( ODBC ).

You can see about this problem book:
ISBN: 0764546996