sql2005 链接服务器 连接Oracle...

一、建立链接服务器

 

       有人喜欢调用系统过程来建立，但我个人对系统过程没有特别的学习 ，所以用的是界面设置，当然有兴趣也可以研究一下的，因为可以把SQL执行导出来。

 USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'TEST2', @srvproduct=N'ORCL', @provider=N'OraOLEDB.Oracle', @datasrc=N'TEST2' GO EXEC master.dbo.sp_serveroption @server=N'TEST2', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'TEST2', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'TEST2', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'TEST2', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'TEST2', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'TEST2', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'TEST2', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'TEST2', @optname=N'use remote collation', @optvalue=N'true' GO USE [master] GO EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TEST2', @locallogin = N'sa', @useself = N'False', @rmtuser = N'IFSAPP', @rmtpassword = N'IFSAPP' GO USE [master] GO EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TEST2', @locallogin = NULL , @useself = N'False', @rmtuser = N'IFSAPP', @rmtpassword = N'IFSAPP' GO

         如上，EXEC master.dbo.sp_addlinkedserver @server = N'TEST2', @srvproduct=N'ORCL', @provider=N'OraOLEDB.Oracle', @datasrc=N'TEST2'   是在系统里建立链接。

          注意，要建立链接之前，要在SQL数据库所在服务器先安装Oracle的客户端，并配置好Oracle的实例连接。

         @server = N'TEST2'   这个是参数设定链接服务器名称。

         @srvproduct=N'ORCL’  这个参数是连接的Oracle的实例名称。

         @provider=N'OraOLEDB.Oracle'  这个参数是连接的驱动，Oracle10G以上装了客户端后重启就可以在SQL里找到。

         @datasrc=N'TEST2'    这个参数是SQL数据库所在电脑建立的Oracle连接的SID（不要与实例名搞混了，这个只是连接名。）.

 

GO
EXEC master.dbo.sp_serveroption @server=N'TEST2', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST2', @optname=N'rpc out', @optvalue=N'true'

 

这两个是打开rpc,rpc out的，默认为False,打开后可以支持远程更改分布式事务。只是用于查询可以不设

 

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TEST2', @locallogin = N'sa', @useself = N'False', @rmtuser = N'IFSAPP', @rmtpassword = N'IFSAPP'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TEST2', @locallogin = NULL , @useself = N'False', @rmtuser = N'IFSAPP', @rmtpassword = N'IFSAPP'
GO

这两个执行是用来设定用于连接Oracle库的用户名及密码，一般执行第一条就行了。但我喜欢把第二条也执行。用户及密码，很直观，不多说了。

 

其它的直接可以默认了，没什么好说的。

 

 

二、查询及更新链接服务器

 

      建立了链接后，就可以用于读取及变动，但语句与SQL还是有点区别的。

    1)、查询

         

         格式1：select * from  [链接服务器名]..[Oracle连接用户名].[Oracle表名]

                select * from  TEST2..IFSAPP.table1

         格式2：select * from  OPENQUERY([链接服务器名],'PL/SQL 语句')

                select * from OPENQUERY(TEST2,'select * from ifsapp.table1')

         格式3：Exec("select * from [Oracle用户].[表1]") AT [链接服务器名]

                Exec("select * from ifsapp.table1") AT TEST2

    2)、更改

         格式1:

             insert   into openquery(TEST2,'select * from ifsapp.table1')     
             select   cols1,cols2,...   from    SQLTable1;

         格式2:

             insert   into openquery(TEST2,'select * from ifsapp.table1')     
             values ('cols1','cols2',...);

          格式3:

             update  openquery(OERP,'select * from ifsapp.table1')  set    cols1= 'LEO',...   where IKEY=4    

 

          格式4：Exec("update [Oracle用户].[表1] set col1=xx where ikey=1") AT [链接服务器名]

                Exec("update IFSAPP.table1  set col1=xx where ikey=1") AT TEST2

          这个格式可以执行Oracle里的存储过程。

其实为了方便，可以把上面的 select * from openquery(TEST2,'select* from ifsapp.table1) 建立成一个视图，然后对视图进行操作就可以了。

 

     3）、执行存储过程
          使用OPENQUERY:
           SELECT *    FROM OPENQUERY(别名, ‘exec 用户名.存储过程名’)

 

三、链接服务器链接ORACLE时遇到“无法启动分布式事务”问题时

 

1. 双方启动MSDTC服务

      MSDTC服务提供分布式事务服务，如果要在数据库中使用分布式事务，必须在参与的双方服务器启动MSDTC（Distributed Transaction Coordinator）服务。

 

2. 打开双方135端口

        MSDTC服务依赖于RPC（Remote Procedure Call (RPC)）服务,RPC使用135端口，保证RPC服务启动，如果服务器有防火墙，保证135端口不被防火墙挡住。

使用"telnet IP 135 "命令测试对方端口是否对外开放。也可用端口扫描软件（比如Advanced Port Scanner）扫描端口以判断端口是否开放。

 

3. 保证链接服务器中语句没有访问发起事务服务器的操作

      在发起事务的服务器执行链接服务器上的查询、视图或存储过程中含有访问发起事务服务器的操作，这样的操作叫做环回（loopback），是不被支持的，所以要保证在链接服务器中不存在此类操作。

 

4. 在事务开始前加入set xact_abort ON语句

     对于大多数 OLE DB 提供程序（包括 SQL Server），必须将隐式或显示事务中的数据修改语句中的 XACT_ABORT 设置为 ON。唯一不需要该选项的情况是在提供程序支持嵌套事务时。

 

5. MSDTC设置

 
       打开"管理工具--组件服务"，以此打开"组件服务--计算机"，在"我的电脑"上点击右键。在MSDTC选项卡中，点击"安全配置"按钮。

在安全配置窗口中做如下设置：

l 选中"网络DTC访问"

l 在客户端管理中选中"允许远程客户端""允许远程管理"

l 在事务管理通讯中选"允许入站""允许出站""不要求进行验证"

l 保证DTC登陆账户为：NT Authority/NetworkService

 

6.1 数据库引擎

      第一种情况的@server或者第二种情况的@datasrc设置为ip地址时，数据库引擎会根据ip地址访问链接服务器，这时不需要做名称解析。

      第一种情况的@server或者第二种情况的@datasrc设置为sql server服务器名时，需要做名称解析，就是把服务器名解析为ip地址。

      有两个办法解析服务器名：

           一是在sql server客户端配置中设置一个别名，将上面的服务器名对应到链接服务器的ip地址。

           二是在"C:/WINDOWS/system32/drivers/etc/hosts"文件中增加一条记录：

           xxx.xxx.xxx.xxx 服务器名

           作用同样是把服务器名对应到链接服务器的ip地址。

 

6.2 DTC

     不管哪一种情况，只要@server设置的是服务器名而不是ip地址，就需要进行名称解析，办法同上面第二种办法，在hosts文件中增加解析记录，上面的第一种办法对DTC不起作用。

如果@server设置的是ip地址，同样不需要做域名解析工作。

 

7. 远程服务器上的名称解析

     分布式事务的参与服务器是需要相互访问的，发起查询的服务器要根据机器名或ip查找远程服务器的，同样远程服务器也要查找发起服务器，远程服务器通过发起服务器的机器名查找服务器，所以要保证远程服务器能够通过发起服务器的机器名访问到发起服务器。

      一般的，两个服务器在同一网段机器名能就行很好的解析，但是也不保证都能很好的解析，所以比较保险的做法是：

      在远程服务器的在"C:/WINDOWS/system32/drivers/etc/hosts"文件中增加一条记录：

      xxx.xxx.xxx.xxx 发起服务器名

 

 8.如果链接服务器链接的不是ORACLE，这样处理基本可以的了，但如果是ORACLE，你会发现还是不可以的。错误依然。这其实跟MSDTC已没有关系了。是ORACLE本身不支持外部启动分布式事务，要解决这个问题，经过找ORACLE官网，他要加一个叫ODAC的补丁,支持分布式事务的一个数据库连接包。

 

Oracle数据库客户端访问组件下载地址:
http://www.oracle.com/technology/software/tech/windows/odpnet/index.html

 

不同的ORACLE版本有对应的不同补丁，选择下载就可以了。

安装好这个补丁后，你再写如：

set xact_abort on    --(这一句必要，不然会报错）

Begin TRAN

     update [链接服务器名]..[Oracle用户名].[Table] set col1='new value'...

IF @@ERROR<>0

     ROLLBACK TRAN;

ELSE

     COMMIT TRAN;

语句成功！

 

有时更新会报找不到列等的错误，可以转换上面提到的几种格式看看，有些格式并不支持一些特别的功能。

 至此，才是真正的成功！

 

四、如果是报"无法初始化链接服务器 "null" 的 OLE DB 访问接口 "OraOLEDB.Oracle" 的数据源对象"

   

    这是因为你的新安装的访问接口OraOLEDB.Oracle没有打开允许操作。重新配置：链接服务器->访问接口->OraOLEDB.Oracle->右键属性，选中 "Allow inprocess" (中文为：允许进程内)

    这个问题解不决。