SQL Server链接服务器访问Oracle

SQLServer链接服务器访问Oracle

一、 测试环境说明

操作系统：Windows Server 2008 R2 64位

数据库版本：SQLServer 2008 R2 64位和Oracle Database 11g 第 2 版 (11.2.0.1.0) 及相应的客户端版本：Oracle 11g client 64位。

其中Oracle Database 11g 安装在一台电脑，另一台安装 SQL Server 2008 R2 64位和Oracle 11g client 64位。

二、 创建SQL Server链接服务

首先SQL Server链接Oracle可以通过两个访问接口：“MSDAORA”和“OraOLEDB.Oracle”。 默认状态下，SQL Server 2008 R2 64位安装后在服务器对象 ->链接服务器 ->访问接口下并没有“MSDAORA”和“OraOLEDB.Oracle”接口。

安装“MSDAORA”访问接口需要下载Oracle Data Access Components（ODAC）for Windows的相应64位版本。安装后重新启动服务器即可以看到“MSDAORA”接口。因为此接口不支持分布式事务，因此不做过多的描述。

安装“OraOLEDB.Oracle”访问接口需要下载Oracle 11g client 64位。具体安装过程如下： 1） 下载后解压zip文件到文件夹，点击setup.exe，弹出如下界面：

直接点[是]。

2） 弹出如下界面：

选择[定制]选项。点击下一步，弹出如下界面：

直接点击下一步，弹出如下界面：

选择软件安装的位置后点击下一步，弹出如下界面：

在可用产品组件窗口点击[全选]后直接点击下一步，弹出如下窗口：

在调度程序代理主机名中输入本机的主机名。点击下一步，弹出如下界面：

在Oracle Services for MTS 中配置端口，这里直接使用默认端口，点击下一步： Oracle Services for Microsoft Transaction Server

Oracle Services for MTS 允许客户在 MTS 协调事务中使用 Oracle 数据库作为资源管理器，从而提供了 Oracle 解决方案和 MTS 之间的强大集成。Oracle Services for MTS 用作 Oracle 数据库到 MTS 分布式交易协同器的代理。因此，这些服务负责提供与 MTS 的客户端连接合并，并允许 COM 组件通过 Oracle 参与 MTS 交易。此外，Oracle Services for MTS 也可以操作运行在任何操作系统上的 Oracle 数据库（如果服务本身在 Windows 上运行）。

直接点击完成，开始安装客户端程序。 3） 配置注册表

打开运行，输入regedit，按回车。

找到注册表项HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\MSDTC\\MTxOCI OracleOciLib值改为oci.dll、 OracleSqlLib值改为orasql11.dll、

OracleXaLib值改为oraclient11.dll。

HKEY_LOCAL_MACHINE\\SOFTWARE\\Wow6432Node\\Microsoft\\MSDTC\\MTxOCI对它做同样的修改。

4） 配置完成后重新启动计算机，继续配置

在开始 ->所有程序 ->Oracle - OraClient11g_home1 ->配置和移植工具下找到Net Manager点击打开，弹出如下窗口：

点击服务命名，选择编辑–>创建弹出网络服务名向导：

在网络服务名中输入要连接的Oracle实例名。点击下一步：

默认选择[TCP/IP(Internet协议)] 点击下一步：

输入要连接的主机名，端口号为1521 ，Oracle的默认端口，点击下一步：

输入服务名（关于Oracle服务名与实例名，请参考Oracle服务名与实例名.doc），点击下一步：

点击测试中的[测试]按钮，进行服务器连接测试，注意要输入正确的用户名和密码，可以直接点击完成跳过测试步骤。

1、“MSDAORA”访问接口是由Microsoft OLE DB Provider for Oracle提供的，建议不使用此接口进行链接。通过该接口建立的链接服务器在进行查询Oracle表时会报错，在带数据类型CLOB、BLOB字段时。

错误提示如下：

“链接服务器\的 OLE DB 访问接口 \返回了消息 \发生了一个 Oracle 错误，但无法从 Oracle 中检索错误信息。\。链接服务器\的 OLE DB 访问接口 \返回了消息 \数据类型不被支持。\

2、“OraOLEDB.Oracle”访问接口是由Oracle 的Oracle Provider for OLE DB 驱动提供的。它解决了两个数据库类型不一致的问题。而且如果需要使用分布式事务，必须使用它来创建链接服务器。

在创建之前，在SQL Server中，链接服务器->访问接口->OraOLEDB.Oracle->右键属性，选中“允许进程内”。

这一步是使我们选择的OraOLEDB.Oracle接口打开执行操作。如未设置会报如下错误： “无法初始化链接服务器 \的OLE DB访问接口 \的数据源对象\

使用Oracle Provider for OLE DB 驱动创建sqlserver链接服务器的代码：

--建立数据库链接服务器

EXECSp_addlinkedserver

@server=N'ORACLEDB',--要创建的链接服务器别名 @srvproduct=N'Oracle',--产品名称

@provider=N'OraOLEDB.Oracle',-- OLE DB 驱动名称 @datasrc=N'ORCL'--数据源

Oracle->ora11g->network->admin->tnsnames.ora查看

EXECSp_addlinkedsrvlogin 'ORACLEDB',--已建立的链接服务器名 'false',-- 固定*/

NULL,--为每个登陆SQL SERVER的用户使用此链接服务器,则写用户名*/ 'scott',--帐号(Oracle) 'tiger'--密码

EXECSp_serveroption 'ORACLEDB', 'rpc out', 'true'

EXECSp_serveroption 'ORACLEDB', 'rpc', 'true'

--这两个是打开rpc,rpc out的，默认为False,打开后可以支持远程更改分布式事务。（如有分布式事务操作必须要设置）

这样我们就建好了链接服务器，已经可以通过它对Oracle数据库进行查询。

三、 链接服务器的查询

查询的两种方式：

1） 链接服务器别名..Oracle名.oracle表，注意使用大写；

SELECT*FROM[ORACLEDB]..[SCOTT].[EMP]

这种方式可以进行操作，优点是使用简单，一目了然，而缺点是性能太差，查询大数据量表很慢。效率太差。

2） 使用OPENQUERY；

对给定的链接服务器执行指定的传递查询。该服务器是 OLE DB 数据源。OPENQUERY 可以在查询的 FROM 子句中引用，就象它是一个表名。OPENQUERY 也可以作为 INSERT、UPDATE 或 DELETE 语句的目标表进行引用。但这要取决于 OLE DB 访问接口的功能。尽管查询可能返回多个结果集，但是 OPENQUERY 只返回第一个。 示例：

A. 执行 SELECT 传递查询

SELECT*

FROMOPENQUERY(ORACLEDB,'SELECT * FROM SCOTT.EMP')

B. 执行 UPDATE 传递查询

UPDATEOPENQUERY (ORACLEDB,'SELECT ENAME FROM SCOTT.EMP WHERE EMPNO = 7369') SETENAME='SMITH';

C. 执行 INSERT传递查询

INSERTOPENQUERY (ORACLEDB,'SELECT * FROM SCOTT.EMP') VALUES (8888,'JIAO','MANAGER',NULL,'1990-12-17 00:00:00.0000000',5000.00,5000.00,10);

D. 执行DELETE传递查询

DELETEOPENQUERY (ORACLEDB,'SELECT ENAME FROM SCOTT.EMP WHERE EMPNO = 8888');

查询方式速度几乎和在Oralce中一样快。并且我们可以将openquery() 当做表来用。

四、 SQL Server链接服务器实现分布式查询

启用条件：

1、 必须安装Oracle Services for Microsoft Transaction Server；

2、 必须用Oracle Provider for OLE DB 驱动提供的OraOLEDB.Oracle访问接口来创建链接服务； 3、 必须双方启动并配置MSDTC服务，关于MSDTC服务的配置请参考：SQLServer分布式事务配置.doc。

示例：

SETXACT_ABORTON BEGINTRAN

UPDATEOpenquery(ORACLEDB,'SELECT * FROM EMP WHERE EMPNO = 7369') SETCOMM= 200

INSERTtest_yiyanhua.dbo.EMP SELECT*

FROMtest_yiyanhua.dbo.EMP WHEREEMPNO= 7499 IF@@ERROR<> 0 ROLLBACKTRAN ELSE COMMITTRAN

本文来源：https://www.bwwdw.com/article/co9r.html