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

有时候我们希望在一个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-bit ODAC 11.2 Release 6 （11.2.0.4.0） Xcopy for Windows x64，安装包名为ODAC112040Xcopy_64bit.zip1.解压缩这个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文件的，要安装后才能用。组件之间的依赖关系。：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_22.安装ODAC包 ,（安装后SQL SERVSER中就会多了一个数据源Oracle provider for OLD DB）在cmd中运行install.bat批处理文件：输入"install.bat all D:OracleClient ODAC"（不含双引号）。这里有几个参数需要特别注意,这里我们传入了三个参数，但最多可以传入四个。第一个参数：all。all代表安装ODAC112040Xcopy_64bit文件夹下所有的组件和客户端。如果你不需要这么多组件，只用到其中一个，比如只用到了OLE DB组件，就可以像下面这样只安装客户端和这一个OLE DB组件： install.bat oledb D:OracleClient ODAC第二个参数：D:OracleClient。这是安装路径，根据自己的实际情况指定，路径中尽量不要有空格和圆括号。第三个参数：ODAC。这个叫ORACLE HOME NAME，这个参数也可以自己随便指定一个字符串，不一定非得是"ODAC"。这个参数是用来写入注册表的。比如，上面这条语句执行后，会在注册表的以下位置写入： HKLMSoftwareOracleKEY_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%in; （在PATH环境变量中添加下面两个路径，用分号隔开: %ORACLE_HOME%;%ORACLE_HOME%in;）二、根据需要配置tnsnames.ora文件前提安装ORACLE 11 64位 client如果要用组件访问Oracle数据库，那么就要根据需要配置tnsnames.ora文件，并存放于%ORACLE_HOME% etworkadmin目录下。我的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的链接服务器注意以下数据源填写的是tnsname.ora文件中左上角的参数名，而不是红色框中的名字运行效果如下 创建一个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"）有人反应64bit系统安装ODAC要安装32bit版本（即oracle 11g Client 为32bit的），否则会出现ODAC无法使用状况。但我测试是没问题的更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址