sp_addlinkedserver (Transact-SQL)

文章目錄

• A. 使用 Microsoft SQL Server Native Client OLE DB 提供者
• B. 使用 Microsoft OLE DB Provider for Microsoft Access
• 使用 Microsoft OLE DB Provider for Oracle
• D. 將 Microsoft OLE DB Provider for ODBC 與 data_source 參數一起使用
• E. 將 Microsoft OLE DB Provider 用於 Excel 試算表
• H. 使用 Microsoft OLE DB Provider for Jet 訪問文字檔
• I. 使用 Microsoft OLE DB Provider for DB2

建立連結的伺服器。連結的伺服器讓使用者可以對 OLE DB 資料來源進行分布式異類查詢。在使用 sp_addlinkedserver 建立連結的伺服器後，可對該伺服器運行分散式查詢。如果連結的伺服器定義為 SQL Server 執行個體，則可執行遠端預存程序。

Transact-SQL 文法約定

 文法

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

 參數

[ @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 Server 將重新導向到 SQL Server Native Client 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 提供者。

[ @provstr = ] ' provider_string '

OLE DB 提供者特定的連接字串，它可標識唯一的資料來源。provider_string 的資料類型為 nvarchar(4000)，預設值為 NULL。provstr 或傳遞給 IDataInitialize 或設定為 DBPROP_INIT_PROVIDERSTRING 屬性以初始化 OLE DB 提供者。

當針對 SQL Server Native Client OLE DB 提供者建立連結的伺服器時，可以使用 SERVER 關鍵字來指定執行個體，即使用 SERVER=servername\instancename 指定特定 SQL Server 執行個體。servername 是運行 SQL Server 的電腦的名稱，instancename 是使用者要串連的特定 SQL Server 執行個體的名稱。

注意：
若要訪問鏡像資料庫，則連接字串必須包含資料庫名稱。該名稱是資料提供者啟用容錯移轉嘗試所必需的。可以在 @provstr 或 @catalog 參數中指定資料庫。此外，連接字串還可以提供容錯移轉夥伴名稱。有關詳細資料，請參閱建立到資料庫鏡像會話的初始串連。

[ @catalog = ] ' catalog '

與 OLE DB 提供者建立串連時所使用的目錄。catalog 的資料類型為 sysname，預設值為 NULL。catalog 作為 DBPROP_INIT_CATALOG 屬性傳遞以初始化 OLE DB 提供者。在針對 SQL Server 執行個體定義連結的伺服器時，目錄指向連結的伺服器映射到的預設資料庫。

 傳回碼值

0（成功）或 1（失敗）

 結果集

無。

 注釋

下表顯示為能通過 OLE DB 訪問資料來源而建立連結的伺服器的方法。對於特定的資料來源，可以使用多種方法為其設定連結的伺服器；該表中可能有多行適用於一種資料來源類型。該表還顯示了用於設定連結的伺服器的 sp_addlinkedserver 參數值。

遠程 OLE DB 資料來源	OLE DB 提供者	product_name	provider_name	data_source	location	provider_string	catalog
SQL Server	Microsoft SQL Server Native Client OLE DB 提供者	SQL Server 1（預設值）
SQL Server	Microsoft SQL Server Native Client OLE DB 提供者		SQLNCLI	SQL Server 的網路名稱（用於預設執行個體）			資料庫名稱（可選）
SQL Server	Microsoft SQL Server Native Client OLE DB 提供者		SQLNCLI	servername\instancename（用於特定執行個體）			資料庫名稱（可選）
Oracle	Microsoft OLE DB Provider for Oracle	任何2	MSDAORA	用於 Oracle 資料庫的 SQL*Net 別名
Oracle，版本 8 及更高版本	Oracle Provider for OLE DB	任何	OraOLEDB.Oracle	用於 Oracle 資料庫的別名
Access/Jet	Microsoft OLE DB Provider for Jet	任何	Microsoft.Jet.OLEDB.4.0	Jet 資料庫檔案的完整路徑
ODBC 資料來源	Microsoft OLE DB Provider for ODBC	任何	MSDASQL	ODBC 資料來源的系統 DSN
ODBC 資料來源	Microsoft OLE DB Provider for ODBC	任何	MSDASQL			ODBC 連接字串
檔案系統	Microsoft OLE DB Provider for Indexing Service	任何	MSIDXS	索引服務類別目錄名稱
Microsoft Excel 試算表	Microsoft OLE DB Provider for Jet	任何	Microsoft.Jet.OLEDB.4.0	Excel 檔案的完整路徑		Excel 5.0
IBM DB2 資料庫	Microsoft OLE DB Provider for DB2	任何	DB2OLEDB			請參閱 Microsoft OLE DB Provider for DB2 文檔。	DB2 資料庫的目錄名稱

1 這種設定連結的伺服器的方式強制連結的伺服器的名稱與 SQL Server 遠程執行個體的網路名稱相同。使用 data_source 指定伺服器。

2“任何”指產品名稱可以任意。

如 果未指定提供者名稱或將 SQL Server 指定為產品名稱，則 Microsoft SQL Server Native Client OLE DB 提供者將是用於 SQL Server 的提供者。即使指定了較早版本的提供者名稱 SQLOLEDB，在儲存到目錄時該名稱也將改為 SQLNCLI。

data_source、location、provider_string 和 catalog 參數標識連結的伺服器所指向的一個或多個資料庫。如果其中任一參數為 NULL，則不設定相應的 OLE DB 初始化屬性。

在群集環境中，當指定指向 OLE DB 資料來源的檔案名稱時，應使用通用命名規則 (UNC) 名稱或共用磁碟機指定位置。

不能在使用者定義的事務內執行 sp_addlinkedserver。

安全記事：
如果使用 sp_addlinkedserver 建立連結的伺服器，則將為所有本地登入添加預設自映射。對於非 SQL Server 提供者，通過 SQL Server 驗證的登入名稱也許能夠以 SQL Server 服務帳戶身份訪問該提供者。管理員應考慮使用 sp_droplinkedsrvlogin <linkedserver_name>, NULL 刪除全域對應。

 許可權

要求具有 ALTER ANY LINKED SERVER 許可權。

 樣本 A. 使用 Microsoft SQL Server Native Client OLE DB 提供者

下面的樣本將建立一個名為 SEATTLESales 的連結的伺服器。產品名稱為 SQL Server，未使用提供者名稱。

USE master;
GO
EXEC sp_addlinkedserver 
   'SEATTLESales',
   N'SQL Server'
GO

下例使用 SQL Server Native Client OLE DB 提供者在 SQL Server 執行個體中建立連結的伺服器 S1_instance1。

EXEC sp_addlinkedserver   
   @server='S1_instance1', 
   @srvproduct='',
   @provider='SQLNCLI', 
   @datasrc='S1\instance1'

B. 使用 Microsoft OLE DB Provider for Microsoft Access

Microsoft.Jet.OLEDB.4.0 提供者串連到使用 2002-2003 格式的 Microsoft Access 資料庫。下面的樣本將建立一個名為 SEATTLE Mktg 的連結的伺服器。

注意：
該樣本假設已安裝 Microsoft Access 和 Northwind 樣本資料庫，並且 Northwind 資料庫位於 C:\Msoffice\Access\Samples 中。

EXEC sp_addlinkedserver 
   @server = 'SEATTLE Mktg', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO

Microsoft.ACE.OLEDB.12.0 提供者串連到使用 2007 格式的 Microsoft Access 資料庫。下面的樣本將建立一個名為 SEATTLE Mktg 的連結的伺服器。

注意：
該樣本假設已安裝 Microsoft Access 和 Northwind 樣本資料庫，並且 Northwind 資料庫位於 C:\Msoffice\Access\Samples 中。

EXEC sp_addlinkedserver 
   @server = 'SEATTLE Mktg', 
   @provider = Microsoft.ACE.OLEDB.12.0', 
   @srvproduct = 'OLE DB Provider for ACE',
   @datasrc = 'C:\MSOffice\Access\Samples\Northwind.accdb'
GO

使用 Microsoft OLE DB Provider for Oracle

下例建立一個名為 LONDON Mktg 的連結的伺服器，它使用 Microsoft OLE DB Provider for Oracle 並假定 Oracle 資料庫的 SQL*Net 別名為 MyServer。

EXEC sp_addlinkedserver
   @server = 'LONDON Mktg',
   @srvproduct = 'Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'MyServer'
GO

D. 將 Microsoft OLE DB Provider for ODBC 與 data_source 參數一起使用

下例建立一個名為 SEATTLE Payroll 的連結的伺服器，它使用 Microsoft OLE DB Provider for ODBC (MSDASQL) 以及 data_source 參數。

注意：
在使用該連結的伺服器之前，必須在該伺服器中將指定的 ODBC 資料來源名稱定義為系統 DSN。

EXEC sp_addlinkedserver 
   @server = 'SEATTLE Payroll', 
   @srvproduct = '',
   @provider = 'MSDASQL', 
   @datasrc = 'LocalServer'
GO

E. 將 Microsoft OLE DB Provider 用於 Excel 試算表

若 要建立使用 Microsoft OLE DB Provider for Jet 訪問 1997 - 2003 格式的 Excel 試算表的連結的伺服器定義，請首先通過指定要在 Excel 工作表中選擇的行和列在 Excel 中建立一個命名範圍。這樣，可以在分散式查詢中將此範圍的名稱引用為表名稱。

EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\MyData\DistExcl.xls',
   NULL,
   'Excel 5.0'
GO

若要訪問 Excel 試算表中的資料，請將單元範圍與名稱相關聯。以下查詢通過使用先前設定的連結的伺服器，將指定的命名範圍 SalesData 作為表來訪問。

SELECT *
   FROM ExcelSource...SalesData
GO

如果 SQL Server 在可以訪問遠程共用的域帳戶下運行，則可以使用 UNC 路徑來代替對應磁碟機。

EXEC sp_addlinkedserver 'ExcelShare',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   '\\MyServer\MyShare\Spreadsheets\DistExcl.xls',
   NULL,
   'Excel 5.0'

若要串連到 Excel 2007 格式的 Excel 試算表，請使用 ACE 提供者。

EXEC sp_addlinkedserver @server = N'ExcelDataSource', 
@srvproduct=N'ExcelData', @provider=N'Microsoft.ACE.OLEDB.12.0', 
@datasrc=N'C:\DataFolder\People.xlsx',
@provstr='EXCEL 12.0' ;

H. 使用 Microsoft OLE DB Provider for Jet 訪問文字檔

以下樣本建立直接存取文字檔的連結的伺服器，而沒有將這些檔案連結為 Access .mdb 檔案中的表。提供者為 Microsoft.Jet.OLEDB.4.0，提供者字串為 Text。

資料來源是包含文字檔的目錄的完整路徑。schema.ini 檔案（描述文字檔的結構）必須與此文字檔存在於相同的目錄中。有關如何建立 Schema.ini 檔案的詳細資料，請參閱 Jet 資料庫引擎文檔。

--Create a linked server.
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 
   'Microsoft.Jet.OLEDB.4.0',
   'c:\data\distqry',
   NULL,
   'Text'
GO

--Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL
GO

--List the tables in the linked server.
EXEC sp_tables_ex txtsrv
GO

--Query one of the tables: file1#txt
--using a four-part name. 
SELECT * 
FROM txtsrv...[file1#txt]

I. 使用 Microsoft OLE DB Provider for DB2

以下樣本建立名為 DB2 的連結的伺服器，該伺服器使用 Microsoft OLE DB Provider for DB2。

EXEC sp_addlinkedserver
   @server='DB2',
   @srvproduct='Microsoft OLE DB Provider for DB2',
   @catalog='DB2',
   @provider='DB2OLEDB',
   @provstr='Initial Catalog=PUBS;
       Data Source=DB2;
       HostCCSID=1252;
       Network Address=XYZ;
       Network Port=50000;
       Package Collection=admin;
       Default Schema=admin;'

 請參閱 參考

分散式查詢預存程序 (Transact-SQL)
sp_addlinkedsrvlogin (Transact-SQL)
sp_addserver (Transact-SQL)
sp_dropserver (Transact-SQL)
sp_serveroption (Transact-SQL)
sp_setnetname (Transact-SQL)
系統預存程序 (Transact-SQL)
系統資料表 (Transact-SQL)
其他資源

為委託配置連結的伺服器
與 SQL Server 一起測試過的 OLE DB 提供者