SQLServer2005创建Oracle10g的链接服务器

2020-11-09 来源：爱问旅游网

Oracle链接服务建立： *安装oracle10g的客户端：使用netmgr添加本地的服务命名，例如：服务命令：DBLINK；测试通过后进行下一步。 *建立ODBC数据源（现在已不需

Oracle链接服务建立：
　　* 安装oracle10g 的客户端：使用netmgr添加本地的服务命名，例如：服务命令：DBLINK；测试通过后进行下一步。
　　* 建立ODBC数据源（现在已不需要，一般直接用Oracle本地服务名代替，本步可省略）

SQL Server 2005 服务器增加系统数据源：
　　　[控制面板]＝》[管理工具]＝》[数据源(ODBC)]＝》[系统DNS]，添加基于 Oracle 的数据源：数据源名为：DBLINK(此名称尽量与Oracle的本地服务名一致),并进行连接测试。

　　* 通过执行SQLServer存储过程来创建链接服务(直接使用Oracle本地服务名，这里本地服务名为CMCC)：
　　　exec sp_addlinkedserver @server='LINK2ORACLE', @srvproduct='Oracle', @provider='MSDAORA', @datasrc=\'#\'" /p>

　　* 链接登录配置：
　　　exec sp_addlinkedsrvlogin 'LINK2ORACLE',false,'sa','OracleUserName','OraclePassword' ;
　　　说明：此语句把远方DBServer的scott用户映射到本地的sa（该用户请根据实际进行更改）。

链接服务器应用:
　　A、查询Oracle数据表方式一(这种方式，当Oracle与SQLServer的数据类型不一致时经常报错,且速度稍慢)：
　　select * from [LINK2ORACLE]..[ORACLE_USER_NAME].TABLE_NAME;
　　我在执行该语句经常报类似错误信息：链接服务器 "LINK2ORACLE" 的 OLE DB 访问接口 "MSDAORA" 为列提供的元数据不一致。对象 ""CMCC"."OS2_GIS_CELL"" 的列 "ISOPENED" (编译时序号为 20)在编译时有 130 的 "DBTYPE"，但在运行时有 5。

　　B、查询Oracle数据表方式二(经试验，这种方式使用起来很顺畅，不报错，且速度几乎和在Oralce中一样快)：
　　select * from openquery(LINK2ORACLE,'select * from OracleUserName.TableName')
　　您可以把openquery()当成表来使用。

　　C、举个例子（将Oralce用户CMCC下的基站表OS_GIS_BASESTATION导入到SQLServer2005数据库中）：
　　select * into OS_GIS_BASESTATION from openquery(LINK2ORACLE,'select * from CMCC.OS_GIS_BASESTATION')

　　D、更便捷的方式：通过创建同义词进行便捷查询:
　　CREATE SYNONYM OS_GIS_CELL FOR [ORACLELK]..[CMCC].OS_GIS_CELL;
　　select * from os_gis_cell;
　　select * from os_gis_cell a where a.CellName is null;

注意：涉及 Oracle 部分的 SQL 语句，尤其是 [ORACLELINK]..[ORACLE_USER_NAME].TABLE_NAME 一定要大写，否则会报类似错误：
消息 7314，级别 16，状态 1，第 1 行
链接服务器 "ORACLELK" 的 OLE DB 访问接口 "MSDAORA" 不包含表 ""CMCC"."OS2_gis_CELL""。该表不存在，或者当前用户没有访问该表的权限。

附:《链接服务器更详细的用法说明》
USE [master]
GO
EXEC --添加服务
master.dbo.sp_addlinkedserver --命令名称
@server = N'TEST', --参数1，连接oracle的数据源名称
@srvproduct=N'ORACLE', --参数2，连接的数据源的产品名称
@provider=N'MSDAORA', --参数3，访问的接口方式
@datasrc=N'ERPORA' --参数4，香港空间，被访问的数据源名称
GO
EXEC --添加用户
master.dbo.sp_addlinkedsrvlogin --命令名称
@rmtsrvname = N'TEST', --数据源名称
@locallogin = NULL , --本地登陆
@useself = N'False', --指定用用户名和密码登陆
@rmtuser = N'SCOTT', --用户名称
@rmtpassword = N'a123456' --用户密码
go

select * from TEST..ERP.BAS_DEPT --测试结果

USE [master]
GO
EXEC --从本地 SQL Server 实例中的已知远程服务器和链接服务器的列表中删除服务器。
master.dbo.sp_dropserver
@server=N'TEST',
@droplogins='droplogins'
GO

/*语法

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
[ , [ @provider= ] 'provider_name' ]
[ , [ @datasrc= ] 'data_source' ]
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string' ]
[ , [ @catalog= ] 'catalog' ]

Oracle Microsoft OLE DB Provider for Oracle MSDAORA 用于 Oracle 数据库的 SQL*Net 别名
Oracle，版本 8 及更高版本 Oracle Provider for OLE DB OraOLEDB.Oracle 用于 Oracle 数据库的别名

参数
[ @server = ] 'server'
要创建的链接服务器的名称。server 的数据类型为 sysname，没有默认值。

[ @srvproduct = ] 'product_name'
要添加为链接服务器的 OLE DB 数据源的产品名称。product_name 的数据类型为 nvarchar(128)，网站空间，默认值为 NULL。如果为 SQL Server，则不必指定 provider_name、data_source、location、provider_string 和 catalog。

[ @provider = ] 'provider_name'
与此数据源对应的 OLE DB 访问接口的唯一编程标识符 (PROGID)。对于当前计算机中安装的指定 OLE DB 访问接口，provider_name 必须唯一。provider_name 的数据类型为 nvarchar(128)，默认值为 NULL；但如果忽略 provider_name，则使用 SQLNCLI。SQLNCLI 是 SQL 本机 OLE DB 访问接口。OLE DB 访问接口应以指定的 PROGID 在注册表中注册。

[ @datasrc = ] 'data_source'
由 OLE DB 访问接口解释的数据源的名称。data_source 的数据类型为 nvarchar(4000)。data_source 作为 DBPROP_INIT_DATASOURCE 属性传递以初始化 OLE DB 访问接口。

[ @location = ] 'location'
由 OLE DB 访问接口解释的数据库的位置。location 的数据类型为 nvarchar(4000)，默认值为 NULL。location 作为 DBPROP_INIT_LOCATION 属性传递以初始化 OLE DB 访问接口。

显示全文

全部栏目

SQLServer2005创建Oracle10g的链接服务器