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