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_addlinkedserver
各種provider參考sp_addlinkedserver
SQL Server對OLE DB provider的支援情況可參考OLE DB providers Tested with SQL Server

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.