sp_addlinkedserver (Transact-SQL)

發行項
06/18/2013

建立連結伺服器。連結的伺服器可讓您對 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)。 provider_name 對於安裝在目前電腦上的指定 OLE DB 提供者來說，必須是唯一的。 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 會當做 DDBPROP_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=servername\instancename 格式的 SERVER 關鍵字指定特定的 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	provider_string	catalog
SQL Server	Microsoft SQL Server Native Client OLE DB Provider	SQL Server1 (預設值)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	SQL Server 的網路名稱 (適用於預設執行個體)		資料庫名稱 (選擇性)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	servername\instancename (適用於特定的執行個體)		資料庫名稱 (選擇性)
Oracle 第 8 版和更新的版本	Oracle 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 Provider 就是搭配 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 Provider

下列範例會建立一個名為 SEATTLESales 的連結伺服器。產品名稱是 SQL Server，另外，不使用任何提供者名稱。

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

下列範例會利用 SQL Server Native Client OLE DB 提供者，在 SQL Server 執行個體上建立連結伺服器 S1_instance1。

EXEC sp_addlinkedserver   
   @server=N'S1_instance1', 
   @srvproduct=N'',
   @provider=N'SQLNCLI', 
   @datasrc=N'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 = N'SEATTLE Mktg', 
   @provider = N'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = N'OLE DB Provider for Jet',
   @datasrc = N'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 = N'SEATTLE Mktg', 
   @provider = N'Microsoft.ACE.OLEDB.12.0', 
   @srvproduct = N'OLE DB Provider for ACE',
   @datasrc = N'C:\MSOffice\Access\Samples\Northwind.accdb';
GO

C.搭配 data_source 參數來使用 Microsoft OLE DB Provider for ODBC

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

[!附註]

您必須先在伺服器中，將指定的 ODBC 資料來源名稱定義為系統 DSN，才可以使用該連結伺服器。

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

D.使用適用於 Excel 試算表的 Microsoft OLE DB Provider

若要利用 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=N'EXCEL 12.0' ;

E.使用 Microsoft OLE DB Provider for Jet 來存取文字檔

下列範例會建立一個連結伺服器，直接存取文字檔，而不將這些檔案當做 Access .mdb 檔中的資料表加以連結。提供者是 Microsoft.Jet.OLEDB.4.0，而提供者字串是 Text。

資料來源是包含這些文字檔之目錄的完整路徑。描述文字檔結構的 schema.ini 檔，必須與文字檔置於同一個目錄下。如需有關如何建立 Schema.ini 檔的詳細資訊，請參閱 Jet Database Engine 文件集。

--Create a linked server.
EXEC sp_addlinkedserver txtsrv, N'Jet 4.0', 
   N'Microsoft.Jet.OLEDB.4.0',
   N'c:\data\distqry',
   NULL,
   N'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];

F.使用 Microsoft OLE DB Provider for DB2

下列範例會建立一個名叫 DB2 的連結伺服器，該連結伺服器是使用 Microsoft OLE DB Provider for DB2。

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