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

时间:2024-01-11 15:13:50

有时候我们希望在一个sqlserver下访问另一个sqlserver数据库上的数据,或者访问其他oracle数据库上的数据,要想完成这些操作,我们首要的是创建数据库链接。
数据库链接能够让本地的一个sqlserver登录用户映射到远程的一个数据库服务器上,并且像操作本地数据库一样。

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;)

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

二、根据需要配置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

在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')

有人反应64bit系统安装ODAC要安装32bit版本(即oracle 11g Client 为32bit的),否则会出现ODAC无法使用状况。但我测试是没问题的