在64位SQL Server中创建Oracle的链接服务器 Link Server

273次阅读

共计 5237 个字符，预计需要花费 14 分钟才能阅读完成。

有时候我们希望在一个 SQL Server 下访问另一个 SQL Server 数据库上的数据，或者访问其他 Oracle 数据库上的数据，要想完成这些操作，我们首要的是创建数据库链接。

数据库链接能够让本地的一个 SQL Server 登录用户映射到远程的一个数据库服务器上，并且像操作本地数据库一样。

SQL Server Link Server
使用 MS SQL Server 提供的 Linked Server 建立对其他异构数据库服务器的单向信任连接, 实现数据传输。
支持 Distribution Transaction, 效能较好, 但存在平台限制, 连接仅能设置在 SQL Server 端, 使用较透明。
使用时 SQL 语法与常用方法差别很小, 很利于开发人员快速适应使用。

适合同时使用以 SQL Server 为主的多种数据库且有数据关联的项目, 使用教透明, 在开发中基本不必考虑异
构数据库的影响, 且能提供完善的事务支持。

一、Oracle 数据访问组件 ODAC 的安装方法 -----安装驱动

注意：安装驱动的目的是让 SQLSERSSVER 中数据源有 Oracle provider for OLD DB，MSSQL2008 默认是自带这个驱动，以下是无需安装的

方一：
如果用 32 位 win2003 操作系统，会有系统自带的 Microsoft OLE DB Provider for Oracle 驱动, 因此不需要做额外的准备工作，但如果使用的是 64 位系统，那么这个驱动是没有的，并且微软也不提供这个驱动的 64 位版本，因此，此时只能使用 Oracle 提供的 Oracle Provider for OLE DB 这个驱动。
安装一个完整的 Oracle11gR2 程序也是一个有效的方法，但这个 64 位版本的安装包有 2 个多 G，相当庞大，如果仅仅是为了建立链接服务器，完全没必要装这么个庞然大物。其实我们只需要到 Oracle 官网上下载 ODAC 压缩包即可。
方一：
下载地址 http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html
我下载的是 ODAC 11.2 Release 4 and Oracle Developer Tools for Visual Studio (11.2.0.3.0)

在 64 位 SQL Server 中创建 Oracle 的链接服务器 Link Server

本文采取方二

方二：下载 64-bit ODAC 11.2 Release 6 (11.2.0.4.0) Xcopy for Windows x64，安装包名为 ODAC112040Xcopy_64bit.zip

1. 解压缩这个 zip 包，把里面的文件都解压到一个文件夹里，我是解压到 D:\ODAC112040Xcopy_64bit，然后打开 cmd，进入到这个文件夹。
这个文件夹里有 8 个子文件夹，分别是：
instantclient_11_2：这是 Oracle 客户端，所有组件的运行都依赖于这个客户端；是最基础的
oledb：这是 OLE DB 组件
asp.net：这是 ASP.NET2 组件
asp.net4：这是 ASP.NET4 组件
odp.net4：这是.NET4 组件
odp.net20：这是.NET2 组件
oramts：这是 Oracle 的 MTS 服务
network：这个文件夹不用管，它是用来存放 tnsnames.ora 文件的，要安装后才能用。

在 64 位 SQL Server 中创建 Oracle 的链接服务器 Link Server

组件之间的依赖关系。：

instantclient_11_2 是最基础的
oledb 依赖于 instantclient_11_2
asp.net 依赖于 instantclient_11_2 和 odp.net20
asp.net4 依赖于 instantclient_11_2 和 odp.net4
odp.net4 依赖于 instantclient_11_2
odp.net20 依赖于 instantclient_11_2
oramts 依赖于 instantclient_11_2

2. 安装 ODAC 包 ,（安装后 SQL SERVSER 中就会多了一个数据源 Oracle provider for OLD DB）

在 cmd 中运行 install.bat 批处理文件：输入 ”install.bat all D:\OracleClient ODAC“（不含双引号）。这里有几个参数需要特别注意, 这里我们传入了三个参数，但最多可以传入四个。

在 64 位 SQL Server 中创建 Oracle 的链接服务器 Link Server

第一个参数：all。all 代表安装 ODAC112040Xcopy_64bit 文件夹下所有的组件和客户端。如果你不需要这么多组件，只用到其中一个，比如只用到了 OLE DB 组件，就可以像下面这样只安装客户端和这一个 OLE DB 组件：install.bat oledb D:\OracleClient ODAC

第二个参数：D:\OracleClient。这是安装路径，根据自己的实际情况指定，路径中尽量不要有空格和圆括号。

第三个参数：ODAC。这个叫 ORACLE HOME NAME，这个参数也可以自己随便指定一个字符串，不一定非得是 ”ODAC”。这个参数是用来写入注册表的。比如，上面这条语句执行后，会在注册表的以下位置写入：HKLM\Software\Oracle\KEY_ODAC 这 ”KEY_” 后面的 ODAC 就是你在参数中传入的那个 ”ODAC”

第四个参数。一般只用前三个参数就够了，在安装组件的时候会自动把它依赖的组件都安装上，但如果你只想安装指定的组件，不想装它所依赖的组件，这时才会用到第四个参数。比如：当你安装 asp.net 时，如果只用前三个参数，会自动把它所依赖的 odp.net20 和 instantclient_11_2 也安装上，但如果你再传入第四个参数 ”false”，如下：install.bat asp.net D:\OracleClient ODAC false 这时就只会安装 asp.net，而不会把 odp.net20 和 instantclient_11_2 也装上。

3. 设置环境变量

ORACLE_HOME=D:\OracleClient (添加一个环境变量 ORACLE_HOME，其值为你的安装路径，比如我这里就是 D:\OracleClient)

PATH=%ORACLE_HOME%;%ORACLE_HOME%\bin; (在 PATH 环境变量中添加下面两个路径，用分号隔开: %ORACLE_HOME%;%ORACLE_HOME%\bin;)

二、根据需要配置 tnsnames.ora 文件

前提安装 ORACLE 11 64 位 client

如果要用组件访问 Oracle 数据库，那么就要根据需要配置 tnsnames.ora 文件，并存放于 %ORACLE_HOME%\network\admin 目录下。我的 tnsnames.ora 文件格式如下，供参考：

NALGENE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.1.127)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = nalgene)
)
)

在 64 位 SQL Server 中创建 Oracle 的链接服务器 Link Server

三、在 64 位 SQL Server 中创建 Oracle 的链接服务器

注意以下数据源填写的是 tnsname.ora 文件中左上角的参数名，而不是红色框中的名字

在 64 位 SQL Server 中创建 Oracle 的链接服务器 Link Server

运行效果如下

在 64 位 SQL Server 中创建 Oracle 的链接服务器 Link Server

创建一个 sqlserver 对 Oracle 的数据库链接：

create view vvv as select * from TESTORACLE..SCOTT.SYS_USER
select * from vvv;
其中 SCOTT 为远程 oracle 数据库服务器的一个用户名，SYS_USER 为该用户在该服务器上的一个表，要非常注意的是：数据库链接（TESTORACLE）后面有两个点（..），再往后面必须全部大写，查询的对象一般为表格或者视图，不能查询同义词。

—1. 配置 LinkServer 语句如下 (链接服务器为 Oracle, 使用 oracle 的 oledb 库 MSDAORA)
—1.1 为建立链接服务器
EXEC master.dbo.sp_addlinkedserver
@server = N‘TESTORACLE‘ — 要创建的链接服务器名称
, @srvproduct=N‘ORACLE‘ — 产品名称
, @provider=N‘OraOLEDB.Oracle‘ —OLE DB 字符
, @datasrc=N‘SEINESCM_97‘ — 数据源

—1.2 为创建连接服务器登陆
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N‘TESTORACLE‘
,@useself=N‘False‘
,@locallogin=NULL
,@rmtuser=N‘SYSTEM‘
,@rmtpassword=‘########‘

GO

—1.3 为设置链接服务器属性
exec sp_serveroption @server=‘TESTORACLE‘,
@optname = ‘rpc‘, — 从给定的服务器启动 rpc
@optvalue =‘true‘ —这样可以调用远程的存储过程

exec sp_serveroption @server=‘TESTORACLE‘,
@optname = ‘rpc out‘, — 对给定的服务器启动 rpc
@optvalue =‘true‘ —在远程执行 DDL 语句

—2. 执行操作语句如下方一：
select * from TESTORACLE..SCOTT.EMP
—将 ORACLE 数据插入到 SQLSERSVER
select * into aaa from TESTORACLE..SCOTT.EMP
—往 ORALCE 插入数据
insert into TESTORACLE..SCOTT.EMP values(9999,‘ename‘,‘job‘,888,getdate(),10000,1000,20)
update TESTORACLE..SCOTT.EMP set empno=8888 where empno=9999
delete from TESTORACLE..SCOTT.EMP where empno=8888

—如下语法创建表出错对象名称 ‘TESTORACLE..SCOTT.EMP2’ 包含的前缀超出了最大限值。最多只能有 2 个。
create table TESTORACLE..SCOTT.EMP2(iddd number)
—使用如下语法才可以创建表
exec(‘create table SCOTT.EMP2(iddd number)‘) at [TESTORACLE]

—创建视图
create view v_test as select * from TESTORACLE..SCOTT.EMP
select * from v_test

—3. 执行操作语句如下方二：
—3.1 为在远程服务器上执行 DDL 语句新建表，用 oracle 的方式书写语句
exec(‘create table STG.TEST(iddd number)‘) at [TESTORACLE]

—3.2 为 opendatasource 函数，查询一下 oracle 中的数据 ok
select * from openquery([TESTORACLE],‘select * from STG.TEST‘)
—等待于
select * from TESTORACLE..SCOTT.EMP

select *
from
opendatasource(‘OraOLEDB.Oracle‘,‘Data Source=SEINESCM_97;
User ID=scott;
password=scott‘)..SCOTT.TEST

—3.3 用 openrowset 函数，把 oracle 的数据直接新建表的同时插入数据
select *
INTO EMP_X
from openrowset(‘OraOLEDB.Oracle‘,‘SEINESCM_97‘;‘scott‘;‘scott‘,SCOTT.EMP)

select *
INTO EMP_X
from openrowset(‘OraOLEDB.Oracle‘,‘SEINESCM_97‘;‘scott‘;‘scott‘,‘select * from SCOTT.EMP‘)