使用方法

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.

轉載于:https://www.cnblogs.com/iwteih/archive/2010/01/18/1650498.html

總結

以上是生活随笔為你收集整理的SQL Server链接服务器 Linked Server的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。