【网学网提醒】：网学会员鉴于大家对十分关注，论文会员在此为大家搜集整理了“SQLServer2005创建到Oracle10g的链接服务器”一文，供大家参考学习
链接服务建立：
*安装oracle10g的客户端：使用netmgr添加本地的服务命名，例如：服务命令：DBLINK；测试通过后进行下一步。
*建立ODBC数据源（现在已不需要，一般直接用Oracle本地服务名代替，本步可省略）
为SQLServer2005服务器增加系统数据源：
[控制面板]＝》[管理工具]＝》[数据源(ODBC)]＝》[系统DNS]，添加基于Oracle的数据源：数据源名为：DBLINK(此名称尽量与Oracle的本地服务名一致),并进行连接测试。
*通过执行SQLServer存储过程来创建链接服务(直接使用Oracle本地服务名，这里本地服务名为CMCC)：
execsp_addlinkedserver@server='LINK2ORACLE',@srvproduct='Oracle',@provider='MSDAORA',@datasrc='CMCC'
*链接登录配置：
execsp_addlinkedsrvlogin'LINK2ORACLE',false,'sa','OracleUserName','OraclePassword';
说明：此语句把远方DBServer的scott用户映射到本地的sa（该用户请根据实际进行更改）。
链接服务器应用:
A、查询Oracle数据表方式一(这种方式，当Oracle与SQLServer的数据类型不一致时经常报错,且速度稍慢)：
select*from[LINK2ORACLE]..[ORACLE_USER_NAME].TABLE_NAME;
我在执行该语句经常报类似错误信息：链接服务器"LINK2ORACLE"的OLEDB访问接口"MSDAORA"为列提供的元数据不一致。对象""CMCC"."OS2_GIS_CELL""的列"ISOPENED"(编译时序号为20)在编译时有130的"DBTYPE"，但在运行时有5。
B、查询Oracle数据表方式二(经试验，这种方式使用起来很顺畅，不报错，且速度几乎和在Oralce中一样快)：
select*fromopenquery(LINK2ORACLE,'select*fromOracleUserName.TableName')
您可以把openquery()当成表来使用。
C、举个例子（将Oralce用户CMCC下的基站表OS_GIS_BASESTATION导入到SQLServer2005数据库中）：
select*intoOS_GIS_BASESTATIONfromopenquery(LINK2ORACLE,'select*fromCMCC.OS_GIS_BASESTATION')
D、更便捷的方式：通过创建同义词进行便捷查询:
CREATESYNONYMOS_GIS_CELLFOR[ORACLELK]..[CMCC].OS_GIS_CELL;
select*fromos_gis_cell;
select*fromos_gis_cellawherea.CellNameisnull;
注意：涉及Oracle部分的SQL语句，尤其是[ORACLELINK]..[ORACLE_USER_NAME].TABLE_NAME一定要大写，否则会报类似错误：
消息7314，级别16，状态1，第1行
链接服务器"ORACLELK"的OLEDB访问接口"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*fromTEST..ERP.BAS_DEPT--测试结果
USE[master]
GO
EXEC--从本地SQLServer实例中的已知远程服务器和链接服务器的列表中删除服务器。
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']
OracleMicrosoftOLEDBProviderforOracleMSDAORA用于Oracle数据库的SQL*Net别名
Oracle，版本8及更高版本OracleProviderforOLEDBOraOLEDB.Oracle用于Oracle数据库的别名
参数
[@server=]'server'
要创建的链接服务器的名称。server的数据类型为sysname，没有默认值。
[@srvproduct=]'product_name'
要添加为链接服务器的OLEDB数据源的产品名称。product_name的数据类型为nvarchar(128)，默认值为NULL。如果为SQLServer，则不必指定provider_name、data_source、location、provider_string和catalog。
[@provider=]'provider_name'
与此数据源对应的OLEDB访问接口的唯一编程标识符(PROGID)。对于当前计算机中安装的指定OLEDB访问接口，provider_name必须唯一。provider_name的数据类型为nvarchar(128)，默认值为NULL；但如果忽略provider_name，则使用SQLNCLI。SQLNCLI是SQL本机OLEDB访问接口。OLEDB访问接口应以指定的PROGID在注册表中注册。
[@datasrc=]'data_source'
由OLEDB访问接口解释的数据源的名称。data_source的数据类型为nvarchar(4000)。data_source作为DBPROP_INIT_DATASOURCE属性传递以初始化OLEDB访问接口。
[@location=]'location'
由OLEDB访问接口解释的数据库的位置。location的数据类型为nvarchar(4000)，默认值为NULL。location作为DBPROP_INIT_LOCATION属性传递以初始化OLEDB访问接口。
[@provstr=]'provider_string'
OLEDB访问接口特定的连接字符串，它可标识唯一的数据源。provider_string的数据类型为nvarchar(4000)，默认值为NULL。provstr或传递给IDataInitialize或设置为DBPROP_INIT_PROVIDERSTRING属性以初始化OLEDB访问接口。
在针对SQL本机客户端OLEDB访问接口创建链接服务器后，可将SERVER关键字用作SERVER=servername/instancename来指定实例，以指定特定的SQLServer实例
。servername是运行SQLServer的计算机名称，instancename是用户将连接到的特定SQLServer实例的名称。
[@catalog=]'catalog'
与OLEDB访问接口建立连接时所使用的目录。catalog的数据类型为sysname，默认值为NULL。catalog作为DBPROP_INIT_CATALOG属性传递以初始化OLEDB访问接口。在针对SQLServer实例定义链接服务器时，目录指向链接服务器映射到的默认数据库。
*/
/*语法
sp_addlinkedsrvlogin[@rmtsrvname=]'rmtsrvname'
[,[@useself=]'useself']
[,[@locallogin=]'locallogin']
[,[@rmtuser=]'rmtuser']
[,[@rmtpassword=]'rmtpassword']
参数
[@rmtsrvname=]'rmtsrvname'
应用登录映射的链接服务器的名称。rmtsrvname的数据类型为sysname，没有默认值。
[@useself=]'useself'
确定用于连接远程服务器的登录名。useself的数据类型为varchar(8)，默认值为TRUE。
值为true时指定登录使用自己的凭据连接rmtsrvname，忽略rmtuser和rmtpassword参数。false指定使用rmtuser和rmtpassword参数连接指定locallogin的rmtsrvname。如果rmtuser和rmtpassword也设置为NULL，则不使用登录名或密码来连接链接服务器。
[@locallogin=]'locallogin'
本地服务器上的登录。locallogin的数据类型为sysname，默认值为NULL。NULL指定此项应用于连接到rmtsrvname的所有本地登录。如果不为NULL，则locallogin可以是SQLServer登录或Windows登录。对于Windows登录来说，必须以直接的方式或通过已被授权访问的Windows组成员身份授予其访问SQLServer的权限。
[@rmtuser=]'rmtuser'
当useself为false时，表示用于连接rmtsrvname的用户名。rmtuser的数据类型为sysname，默认值为NULL。
[@rmtpassword=]'rmtpassword'
与rmtuser关联的密码。rmtpassword的数据类型为sysname，默认值为NULL。
*/
/*语法
sp_dropserver[@server=]'server'
[,[@droplogins=]{'droplogins'|NULL}]
参数
[@server=]'server'
要删除的服务器。server的数据类型为sysname，无默认值。server必须存在。
[@droplogins=]'droplogins'|NULL
指示如果指定了droplogins，那么对于server，还必须删除相关的远程服务器和链接服务器登录名。@droplogins的数据类型为char(10)，默认值为NULL。
*/
/*
前提条件
1)在SQL_SERVER2005服务器上安装Oracle9i的客户端。
假设安装到F:/oracle目录。
注意需将ORACLE安装后的目录设为Everyone权限。
(F:/oracle目录增加AuthenticatedUsers用户,将‘读取和运行’权限取消掉，再勾起来，重启windows！)
2)配置F:/oracle/product/10.2.0/client_2/network/ADMIN/tnsnames.ora文件。
配置示例:
HAODAIFU=
(DESCRIPTION=
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.8.119)(POR
T=1521)))
(CONNECT_DATA=(SERVICE_NAME=haodaifu))
)
3)在DOS模式下运行以下命令以便确认ORACLE客户端安装无误。
a:sqlplusscott/a123456@haodaifu
b:tnspinghaodaifu
C:/DocumentsandSettings/Administrator>tnspinghaodaifu
TNSPingUtilityfor32-bitWindows:Version11.1.0.6.0-Productionon13-12月-200704:36:15
Copyright(c)1997,2007,Oracle.Allrightsreserved.
已使用的参数文件:
D:/99.88.66.software/02.oracle/DB11G/11G/network/admin/sqlnet.ora
已使用TNSNAMES适配器来解析别名
Attemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=192.168.8.122)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=haodaifu))
)
OK(10毫秒)
4)打开控制面板－服务，确认DistributedTransactionCoordinator服务已经启动。
5)修改注册表HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/MSDTC/MTxOCI
OracleOciLib=oci.dll
OracleSqlLib=orasql9.dll
OracleXaLib=oraclient9.dll
6)重启SQL_SERVER服务器
7)图形创建链接服务器方法--〉
A)打开SQLSERVERManagementStudio，新建链接服务器。
B)链接服务器：写上链接服务器的名字，如：test
C)访问接口：选择MicrosoftOLEDBProviderforOracle
D)产品名称：写上Oracle
E)数据源：写上tnsnames.ora文件中配置的服务名，如：haodaifu
F)访问接口字符串：userid=用户名;password=口令(可以省略)
G)选择安全性选项页，使用此安装上下文建立连接：
a.远程登录：scott
b.使用密码：tiger
H)确定
8)SQL的写法有两种
a)使用T-SQL语法:
SELECT*FROMLNK1..用户名.表名--注意用户名称，表名称要大写
b)使用PLSQL语法：
select*fromopenquery(LNK1,'select*from用户名.表名')
第二种访问方式比第一种约快50％；第二种访问方式跟直连ORACLE的速度相当；
第一种访问方式可能会导致一些意外错误，如：该表不存在，或者当前用户没有访问该表的权限。
如果需要访问的column中使用没有精度的数据类型，这两种查询方式都可能会报错，这是ORACLE的BUG，
无法修正，只能通过查询语句的特殊处理规避这一问题：
OLEDB提供程序'OraOLEDB.Oracle'为列提供的元数据不一致。执行时更改了元数据信息。
