Hi all,

   I followed the following below ariticle in dbasupport and dbajournal for about connectivity from Oracle Server 10.1.0.2.0   to SQL Server 2005
http://www.dbasupport.com/oracle/ora10g/connection01.shtml  
http://www.databasejournal.com/features/oracle/article.php/3442661/Making-a-Connection-from-Oracle-to-SQL-Server.htm

 
i configured  the listener.ora ,tnsnames.or and inityscco2.ora

$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/101/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = yscc02)
      (ORACLE_HOME = /u01/app/oracle/product/101)
      (PROGRAM = extproc)
   ) 
 (SID_DESC =
      (SID_NAME = yscc02)
      (ORACLE_HOME = /u01/app/oracle/product/101)
      (PROGRAM = hsodbc)
 (ENVS = "LD_LIBRARY_PATH=/u01/app/oracle/product/101/lib32:/usr/lib:/u01/app/oracle/product/101/hs/lib32")   
  ) 
 )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ysccapps.yamama.com)(PORT = 1521))
      )
    )
  )

LISTENERYSCCAPPS =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=100.100.50.5)(PORT=1522))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENERYSCCAPPS=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME= yscapps)
         (ORACLE_HOME = /u01/app/oracle/product/101 )
         (PROGRAM=hsodbc)
       )
      )


   $lsnrctl status listener

LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.2.0 - Production on 20-JUN-2010 14:03:22

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 10.1.0.2.0 - Production
Start Date                15-JUN-2010 14:49:05
Uptime                    4 days 23 hr. 14 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/101/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/101/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ysccapps.yamama.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ysccapps.yamama.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ysccapps.yamama.com)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "yscc02" has 1 instance(s).
  Instance "yscc02", status UNKNOWN, has 2 handler(s) for this service...
Service "yscc02.ysccapps.yamama.com" has 1 instance(s).
  Instance "yscc02", status READY, has 1 handler(s) for this service...
Service "yscc02XDB.ysccapps.yamama.com" has 1 instance(s).
  Instance "yscc02", status READY, has 1 handler(s) for this service...
The command completed successfully


  $ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/101/network/a
dmin/tnsnames.ora
# Generated by Oracle configuration tools.

YSCCAPPS1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ysccapps.yamama.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = yscc02)
      )
     (HS = OK)
     )

$tnsping ysccapps1

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.1.0.2.0 - Production on 20-JUN-2010 14:04:43

Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ysccapps.yamama.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = yscc02)) (HS = OK))
OK (0 msec)

$ sqlplus new/password@ysccapps1

SQL*Plus: Release 10.1.0.2.0 - Production on Sun Jun 20 14:07:01 2010

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> 


$ cat inityscc02.ora
 This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent. 

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = yscc02 
HS_FDS_TRACE_LEVEL = off 
HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>

#
# ODBC specific environment variables
#
set ODBCINI=<full path name of the odbc initilization file>




and i had created  database link like

SQL>create database link ysccapps1
connect to "sa" identified by "passwad"
using 'ysccapps1';

SQL> select * from dbo.status@YSCCAPPS1.YSCCAPPS.YAMAMA.COM;
select 8 from dbo.status@YSCCAPPS1.YSCCAPPS.YAMAMA.COM
                         *
ERROR at line 1:
ORA-28546: connection initialization failed, probable Net8 admin error
ORA-28511: lost RPC connection to heterogeneous remote agent using SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=y
sccapps.yamama.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=yscc02)))
ORA-02063: preceding 2 lines from YSCCAPPS1




Please assist me . i need to periodically pull data from Oracle server to Sql server


Best Regards