SQL-Server链接服务器访问Oracle数据

SQL Server 链接服务器访问 Oracle

离线安装 .NET Framework 3.5
方法一：使用 NetFx3.cab 文件
下载 NetFx3.cab 文件，并将其放置在 Windows 10 系统盘的 C:Windows 文件夹中。
以管理员身份运行命令提示符，输入以下命令并回车：

dism /online /Enable-Feature /FeatureName:NetFx3 /Source:"%windir%" /LimitAccess

等待部署进度达到 100%，无报错即可。
在“程序和功能”-“Windows 功能”中查看，确认 .NET Framework 3.5 选项已被勾选。
在这里插入图片描述

一、测试环境说明

操作系统：Windows 7 64bit ；Windows Server 2008 R2 64bit

数据库版本：SQL Server 2008 R2 64 位和 Oracle Database 11g 第 2 版 (11.2.0.1.0)

及相应的客户端版本：Oracle 11g client 64 位。

二、创建 SQL Server 链接服务

首先 SQL Server 链接 Oracle 可以通过两个访问接口： “ MSDAORA ” 和“OraOLEDB.Oracle”。

默认状态下，SQL Server 2008 R2 64 位安装后在服务器对象 -> 链接服务器 -> 访问接口下并没有 “OraOLEDB.Oracle”接口。

注意：在安装之前记得关闭杀毒软件：例如火绒，不然会出现：Oracle安装出现CreateFile() error 32 when trying set file time 错误

第一步：安装服务器版本 Oracle Database 11g参考文章

第二步：安装“OraOLEDB.Oracle”访问接口需要下载 Oracle 11g client 64 位。具体安装过程如下：

1）下载后解压 zip 文件到文件夹，点击setup.exe，弹出如下界面：
在这里插入图片描述

直接点击完成，开始安装客户端程序。

3）配置注册表

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

在这里插入图片描述

找到注册表项 HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI

OracleOciLib 值改为 oci.dll、
OracleSqlLib 值改为 orasql11.dll、
OracleXaLib 值改为 oraclient11.dll。

在这里插入图片描述

HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftMSDTCMTxOCI 对它做同样的修改。
4）配置完成后重新启动计算机，继续配置

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

输入服务名，点击下一步：
在这里插入图片描述

点击测试中的[测试]按钮，进行服务器连接测试，注意要输入正确的用户名和密码，可以直接点击完成跳过测试步骤。
注意：配置好net manager之后一定要关闭窗口进行保存，不然sqlserver链接服务器Oracle的时候会一直链接失败

1、 “OraOLEDB.Oracle” 访问接口是由 Oracle 的 Oracle Provider for OLE DB 驱动提供的。它解决了两个数据库类型不一致的问题。而且如果需要使用分布式事务，必须使用它来创建链接服务器。
在创建之前，在 SQL Server 中，链接服务器->访问接口->OraOLEDB.Oracle->右键属性，
选中“允许进程内”。

这一步是使我们选择的 OraOLEDB.Oracle 接口打开执行操作。如未设置会报如下错误：

“无法初始化链接服务器 “null” 的 OLE DB 访问接口 “OraOLEDB.Oracle” 的数据源对象"

在这里插入图片描述

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

--建立数据库链接服务器 EXEC Sp_addlinkedserver
@server =N'ORCL',--要创建的链接服务器别名@srvproduct=N'Oracle',--产品名称@provider=N'OraOLEDB.Oracle',-- OLE DB 驱动名称@datasrc=N'ORCL' --数据源
Oracle->ora11g->network->admin->tnsnames.ora查看

EXEC Sp_addlinkedsrvlogin 'ORACLEDB',--已建立的链接服务器名'false',-- 固定*/
NULL,--为每个登陆SQL SERVER的用户使用此链接服务器,则写用户名*/
'YNMZDXWC',--帐号(Oracle) '1' --密码
EXEC Sp_serveroption 'ORACLEDB',
'rpc out', 'true'

EXEC Sp_serveroption

'ORACLEDB',
'rpc', 'true'

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

这样我们就建好了链接服务器，已经可以通过它对Oracle 数据库进行查询。
在这里插入图片描述

三、链接服务器的查询

查询的两种方式：

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

SELECT *FROM OPENQUERY(ZHANG,'SELECT *FROM ELE_DEPARTMENT') SELECT * FROM [ORACLEDB]..[SCOTT].[EMP]

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

2）使用 OPENQUERY；

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

SELECT *
FROM OPENQUERY(ORACLEDB, 'SELECT * FROM SCOTT.EMP') SELECT *
FROM OPENQUERY(ZHANG,'SELECT *FROM ELE_DEPARTMENT')

B.执行 UPDATE 传递查询

UPDATE OPENQUERY (ORACLEDB, 'SELECT ENAME FROM SCOTT.EMP WHERE EMPNO = 7369')
SET ENAME = 'SMITH';

C.执行 INSERT 传递查询

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

D.执行 DELETE 传递查询

DELETE OPENQUERY (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。
示例：

SET XACT_ABORT ON BEGIN TRAN

UPDATE Openquery(ORACLEDB, 'SELECT * FROM EMP WHERE EMPNO = 7369') SET	COMM = 200
INSERT test_yiyanhua.dbo.EMP SELECT *
FROM	test_yiyanhua.dbo.EMP WHERE	EMPNO = 7499
IF @@ERROR <> 0 ROLLBACK TRAN
ELSE
COMMIT TRAN