ORACLE通过odbc数据源连接sqlserver

ms server 2003 oracle 10.2.0.1.0连接ms sql server 2005

1、控制面板--管理工具--obbc数据源管理器 系统DSN 中增加名称为 sjck 的DSN,并测试成功。
2、复制F:\oracle\product\10.2.0\db_1\hs\admin\inithsodbc.ora，新建F:\oracle\product\10.2.0\db_1\hs\admin\initsjck.ora，initsjck.ora内容为：
     HS_FDS_CONNECT_INFO = sjck
     HS_FDS_TRACE_LEVEL = 0
3、在F:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora 文件中
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    ------增加
    (SID_DESC =
      (SID_NAME = sjck)
      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
      (PROGRAM = hsodbc)
    )
   ------------
  )
4、在F:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora文件中增加
sjck=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)) # HOST= ORACLE IP
    (CONNECT_DATA=(SID=sjck))
    (HS=OK)
  )

5、重启监听。
6、在plsql中新建连接
CREATE PUBLIC DATABASE LINK sjck
connect to "id" identified by "password"
using 'sjck';
测试连接
select  * from dw_area@sjck

 

ms server 2008 oracle 11.2.0连接ms sql server 2005

1、同上。

2、复制F:\app\Administrator\product\11.2.0\dbhome_1\hs\admin\initdg4odbc.ora，新建F:\app\Administrator\product\11.2.0\dbhome_1\hs\admin\initdg4sjck.ora，initdg4sjck.ora内容为：
     HS_FDS_CONNECT_INFO = sjck
     HS_FDS_TRACE_LEVEL = 0
3、在F:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora 文件中
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = F:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
    )
    ------增加
    (SID_DESC =
      (PROGRAM = dg4odbc)
      (SID_NAME = dg4sjck)
      (ORACLE_HOME = F:\app\Administrator\product\11.2.0\dbhome_1)
    )

   ------------
  )
4、在F:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora文件中增加
sjck =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))  #HOST = oracle ip
    )
    (CONNECT_DATA =
      (SID = dg4SJCK)
    )
    (HS = OK)
  )

5、重启监听。
6、在plsql中新建连接
CREATE PUBLIC DATABASE LINK sjck
connect to "id" identified by "password"
using 'sjck';
测试连接
select  * from dw_area@sjck