共用方式為

sp_addlinkedserver (Transact-SQL)

  • 發行項

建立連結伺服器。 連結的伺服器可讓您對 OLE DB 資料來源存取分散式異質性查詢。 當您使用 sp_addlinkedserver 建立連結伺服器之後，即可對這部伺服器執行分散式查詢。 如果連結的伺服器定義為 SQL Server 的執行個體，就可以執行遠端預存程序。

適用於：SQL Server (SQL Server 2008 至目前版本)。

主題連結圖示 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

location

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;';

G.加入 Windows Azure SQL 資料庫成為連結伺服器以搭配跨雲端和內部部署資料庫的分散式查詢使用

您可以加入 Windows Azure SQL 資料庫成為連結伺服器，再將其搭配跨內部部署和雲端資料庫的分散式查詢使用。 這是跨內部部署企業網路和 Windows Azure 雲端的資料庫混合式方案的一項元件。

SQL Server 盒裝產品包含分散式查詢功能，可讓您撰寫查詢將本機資料來源的資料與遠端資料來源的資料 (包括非 SQL Server 資料來源的資料) 結合，定義成連結伺服器。 您將能加入每個 Windows Azure SQL 資料庫 (虛擬 master 除外) 成為個別的連結伺服器，然後直接供您的資料庫應用程式使用，與任何其他資料庫無異。

使用 Windows Azure SQL 資料庫的好處包括管理能力、高可用性、延展性、有您熟悉的開發模型和關聯式資料模型。 您的資料庫應用程式的需求決定了該如何在雲端使用 Windows Azure SQL 資料庫。 您可以一次將所有資料移到 Windows Azure SQL 資料庫，或是逐漸搬移部分資料並將其餘資料留在內部部署環境。 藉著這類混合式資料庫應用程式，如今您可以加入 Windows Azure SQL 資料庫成為連結伺服器，而且資料庫應用程式可以發出分散式查詢，將 Windows Azure SQL 資料庫與內部部署資料來源的資料結合。

以下是說明如何使用分散式查詢連接到 Windows Azure SQL 資料庫的簡單範例：

------ Configure the linked server
-- Add one Windows Azure SQL DB as Linked Server
EXEC sp_addlinkedserver
@server='myLinkedServer', -- here you can specify the name of the linked server
@srvproduct='',     
@provider='sqlncli', -- using SQL Server Native Client
@datasrc='myServer.database.windows.net',   -- add here your server name
@location='',
@provstr='',
@catalog='myDatabase'  -- add here your database name as initial catalog (you cannot connect to the master database)
-- Add credentials and options to this linked server
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'myLinkedServer',
@useself = 'false',
@rmtuser = 'myLogin',             -- add here your login on Azure DB
@rmtpassword = 'myPassword' -- add here your password on Azure DB
EXEC sp_serveroption 'myLinkedServer', 'rpc out', true;
------ Now you can use the linked server to execute 4-part queries
-- You can create a new table in the Azure DB
exec ('CREATE TABLE t1tutut2(col1 int not null CONSTRAINT PK_col1 PRIMARY KEY CLUSTERED (col1) )') at myLinkedServer
-- Insert data from your local SQL Server
exec ('INSERT INTO t1tutut2 VALUES(1),(2),(3)') at myLinkedServer

-- Query the data using 4-part names
select * from myLinkedServer.myDatabase.dbo.myTable

請參閱

參考

分散式查詢預存程序 (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)