SQL Server链接服务器 Linked Server

使用方法

exec  sp_addlinkedserver  @server , @srvproduct , @provider , @datasrc , @location , @provstr , @catalog ;
exec  sp_addlinkedsrvlogin  @rmtsrvname , @useself , @locallogin , @rmtuser , @rmtpassword ;
exec  sp_serveroption  @server , @optname , @optvalue ;
sp_helpserver
sp_linkedservers
sp_dropserver

例如：

exec  sp_addlinkedserver  ' ERP ' , ' Oracle ' , ' MSDAORA ' , ' AMT ' ;
exec  sp_addlinkedsrvlogin  ' ERP ' , ' false ' , null , ' crm ' , ' crm ' ;
exec  sp_serveroption  ' ERP ' , ' rpc ' , ' true ' ;

Oracle作为远程链接服务器时，SQL Server服务器上必须安装Oracle Client。配置SQL*Net名供OLE DB provider使用


sp_addlinkedsrvlogin
@rmtsrvname: 远程链接服务器名字
@useself: 是否使用当前登录SQL Server的认证信息登录链接服务器，SQL Server和远程链接服务器均使用Windows集成认证登录时可以使用
@locallogin: 建立本地SQL Server登录帐号与远程链接服务器登录帐号之间的对应关系，例如本地SQL Server sa帐号使用A1登录链接服务器，crm帐号使用A2登录链接服务器。@locallogin可以是域帐号。为null时本地SQL Server所有帐号均可使用该登录信息
@rmtuser,@rmtpassword: 登录远程链接服务器的帐号、密码

查询语句
方法一 ： 使用linked_server_name.catalog.schema.object_name，例如：
select * from ERP..CRM.INQ
linked_server_name     Linked server referencing the OLE DB data source
catalog             Catalog in the OLE DB data source that contains the object
schema             Schema in the catalog that contains the object
object_name         Data object in the schema
    SQL Server用linked_server_name取linked server相关配置信息，然后将catalog、schema、object_name作为参数传递给OLEDB。例如远程链接服务器为SQL Server时catalog为数据库实例名，schema为owner id (dbo)；链接服务器为Oracle时 (OLEDB)，catalog为空，schema为用户

    注意点：
    a). 远程链接服务器为Oracle时schema、object_name必须大写（其他类型的不清楚），否则会报错
消息 7314，级别 16，状态 1，第 1 行
链接服务器 "ERP" 的 OLE DB 访问接口 "MSDAORA" 不包含表 ""CRM"."INQ""。该表不存在，或者当前用户没有访问该表的权限。
    b). 必须使用完整的名称。例如SQL Server本地执行sql，dbo可以省略不写，使用远程链接服务器时则必须提供

方法二 ：使用openquery，例如：
select * 
from openquery(ERP,'select t.* from(select inq.*,rownum as rindex from inq order by img01) t where t.rindex>=51 and t.rindex<=100')

    优点：
    a). SQL Server只是将openquery中的sql发送给远程服务器执行，因此可以使用远程链接服务器的所有sql语法（例如Oracle语法），sql对象也不必大写
    b). 数据类型的异常很少（个别情况下还是会发生）
    c). openquery的写法同样可用于update、insert、delete
    d). 链接服务器为Oracle时，schema不必出现在sql语句中

关于使用链接服务器方面的一些限制、前提条件，可以参考 External Data and Transact-SQL ， Keyset-Driven Cursors Requirements for OLE DB Providers

常见问题
1. 数据类型方面的异常，例如：
Msg 7356, Level 16, State 1, Line 1 链接服务器 "ERP" 的 OLE DB 访问接口"MSDAORA"为列提供的元数据不一致。对象 ""CRM"."INQ"" 的列 "IMG21" (编译时序号为 7)在编译时有131的"DBTYPE"，但在运行时有 130。
    查询语句使用方法一时很容易发生这种错误，使用openquery时很少（个别情况下还是会发生），建议使用openquery。如果openquery仍然发生这种情况，建议将远程链接服务器的数据类型尽量转化为简单、明确的，在SQL Server中有对应的数据类型。例如Oracle的Number就是很灵活的一种，尽量能够让SQL Server能够确定应该转换为decimal还是int类型

2. 字符集转换，例如对远程链接服务器上的记录集进行=、like等比较运算操作时可能会出现字符集错误：
Msg 468, Level 16, State 9, Line 1
无法解决 equal to 操作中 "Chinese_PRC_90_CI_AI" 和 "Chinese_PRC_CI_AS" 之间的排序规则冲突。
    可以使用强制字符集转换解决，例如
select * 
from openquery(ERP,'select t.* from(select inq.*,rownum as rindex from inq order by img01) t where t.rindex>=51 and t.rindex<=100') t
inner join itm_item i on t.img01 collate Chinese_PRC_CI_AS = i.itm_code

3. 权限问题
场景：64位的SQL Server服务器，使用的64位Oracle OLE DB provider（OraOLEDB.Oracle.1），添加Linked Server后，使用SQL Server认证登录的帐号有权限访问，而用Windows集成认证登录的域帐号访问时报错：
消息 7399，级别 16，状态 1，第 1 行
链接服务器 "ERP" 的 OLE DB 访问接口 "OraOLEDB.Oracle.1" 报错。访问被拒绝。
消息 7301，级别 16，状态 2，第 1 行
无法从链接服务器 "ERP" 的 OLE DB 访问接口 "OraOLEDB.Oracle.1" 获取所需的接口("IID_IDBCreateCommand")。
解决方法：
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\OraOLEDB.Oracle.1]
"AllowInProcess"=dword:00000001
如果Providers下面没有OraOLEDB.Oracle.1则新建

相关知识、术语
    SQL Server中使用openquery、openrowset、opendatasource叫做distributed query分布式查询
    SQL Server支持分布式查询主要有2种方式，一种是上面讲到的Linked Server，另外一种叫做Ad Hoc方式，即使用openrowset或者opendatasource。Linked Server用于执行较频繁的情况，Ad Hoc方式用于执行频率不高的情况
    OLE DB provider有2种方式，一种是直接将数据库表作为rowset行集暴露出来，这样客户程序可以通过OLE DB接口操作这个数据库表，这种方式叫做remote tables。另外一种是通过OLE DB接口将查询sql语句发送给数据库服务器，服务器执行查询，将rowset返回给客户程序，这种方式叫做pass-through queries
    前面查询语句的2种方法，方法一应当使用remote tables方式，方法二使用pass-through queries方式。所以方法二完全支持远程链接服务器的SQL语法
    SQL Server将openquery和remote tables返回的rowset当作一个表，查询处理中将它与SQL Server自己的表一样进行处理。sql中可能对openquery的rowset字段下条件过滤、需要排序、与其他表关联等，SQL Server根据OLE DB provider接口提供的信息，确定这些操作能否委托给远程链接服务器。例如a.img02='21cdk' and img10=700这样的条件，如果可以委托给远程链接服务器，则这些条件运算操作将发送给远程服务器进行，返回的是条件过滤之后的rowset，否则只能返回全部数据，由SQL Server对返回的数据执行这2个条件的过滤操作
    OLE DB provider接口能够提供的元数据信息非常有限，不同数据库之间的数据结构造成的数据转换操作等，造成SQL Server对非SQL Server的链接服务器无法进行过多的查询优化策略。使用pass-through queries方式时，远程数据库服务器可以充分利用自己维护的统计信息、索引等优化措施，因此应当尽量使用pass-through queries方式充分的利用链接服务器
    下面例子，erp是一个Oracle的数据库
select a.*
from erp..CRM.INQ a
where a.img02='21cdk' and img10=700
    查询计划
    
    Remote Query返回了INQ表所有数据，Filter操作为CONVERT_IMPLICIT(int,[erp].[CRM].[INQ].[IMG10] as [a].[IMG10],0)=(700) AND [erp].[CRM].[INQ].[IMG02] as [a].[IMG02]='10bbk'

链接服务器配置
对分布式查询，SQL Server支持2个层级的配置：OLE DB provider level，在windows注册表中；linked server level，通过sp_serveroption配置（这2个层级的配置都可以通过SQL Server Enterprise Manager中的链接服务器右键菜单属性进行配置）

OLE DB provider level :
DynamicParameters: OLE DB provider支持参数化方式的查询，并且参数使用?作为标记，可以设置为true（非0值）
SqlServerLike: 支持like操作可以设置为true（非0值）。支持like操作时SQL Server可以将该操作提交给远程服务器执行，否则SQL Server需要自己完成like操作处理。在remote tables方式中SQL Server可能面临这样的决策
DisallowAdhocAccess: 是否允许SQL Server使用ad hoc方式执行分布式查询，设置为true（非0值）或者没有设置，SQL Server都不会允许使用ad hoc方式
IndexAsAccessPath: SQL Server是否可以通过OLE DB provider使用远程服务器的索引信息，需要OLE DB provider实现了相关接口
NonTransactedUpdates: 是否支持事务，配置为true时，即使OLE DB provider实现了事务接口，SQL Server也不会对分布式更新语句使用事务
AllowInProcess: 是否在SQL Server进程内完成OLE DB接口操作。配置为进程内操作，OLE DB的异常可能会影响SQL Server进程，在SQL Server进程外执行OLE DB操作，SQL Server无法更新、插入LOB对象，例如text、image、clob类型。该参数需要SQL Server与远程链接服务器位于同一台机器（是否要求远程链接服务器也是SQL Server？）
LevelZeroOnly: 如果设置为ture，SQL Server只是用OLE DB level0级的接口
NestedQueries: 是否允许嵌套查询

Linked Server level :
sp_serveroption可以配置的服务器选项有:
collation compatible: 
    Affects Distributed Query execution against linked servers. If this option is set to true, Microsoft® SQL Server™ assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order). This enables SQL Server to send comparisons on character columns to the provider. If this option is not set, SQL Server always evaluates comparisons on character columns locally.
    This option should be set only if it is certain that the data source corresponding to the linked server has the same character set and sort order as the local server.
use remote collation:
    设置为true时，对于文本字段SQL Server将使用远程链接服务器的字符集，如果链接服务器是SQL Server，则从SQL Server的OLE DB provider接口获取字符集信息，如果不是SQL Server，则使用collation name配置的字符集。配置为false时SQL Server使用本地服务器的默认字符集
collation name: 字符集
connect timeout:
data access:
    Enables and disables a linked server for distributed query access. Can be used only for sysserver entries added through sp_addlinkedserver.
dist: Distributor.
dpub: Remote Publisher to this Distributor.
lazy schema validation: Determines whether the schema of remote tables will be checked. If true, skip schema checking of remote tables at the beginning of the query.
pub: Publisher.
query timeout: Time-out value for queries against a linked server. If 0, use the sp_configure default.
rpc: Enables RPC from the given server.
rpc out: Enables RPC to the given server.
sub: Subscriber.