sp_addlinkedserver (Transact-SQL)

文章
08/21/2024

建立連結的伺服器。鏈接的伺服器會針對 OLE DB 數據源提供分散式異質查詢的存取權。使用 sp_addlinkedserver建立連結的伺服器之後，即可針對此伺服器執行分散式查詢。如果鏈接的伺服器定義為 SQL Server 的實體，則可以執行遠端預存程式。

注意

Microsoft Entra ID 先前稱為 Azure Active Directory (Azure AD)。

Transact-SQL 語法慣例

語法

sp_addlinkedserver
    [ @server = ] N'server'
    [ , [ @srvproduct = ] N'srvproduct' ]
    [ , [ @provider = ] N'provider' ]
    [ , [ @datasrc = ] N'datasrc' ]
    [ , [ @location = ] N'location' ]
    [ , [ @provstr = ] N'provstr' ]
    [ , [ @catalog = ] N'catalog' ]
    [ , [ @linkedstyle = ] linkedstyle ]
[ ; ]

引數

[ @server = ] N'server'

要建立的連結伺服器名稱。 @server為 sysname，沒有預設值。

[ @srvproduct = ] N'srvproduct'

要新增為連結伺服器的 OLE DB 資料源產品名稱。 @srvproduct為 nvarchar（128），預設值為 NULL。如果值為 SQL Server，則@provider、@datasrc、@location、@provstr和@catalog不需要指定。

[ @provider = ] N'provider'

對應至此數據源之 OLE DB 提供者的唯一程式設計標識碼（PROGID）。在 目前計算機上安裝的指定 OLE DB 提供者，@provider 必須是唯一的。 @provider為 nvarchar（128），預設值為 NULL。

在 SQL Server 2019 （15.x）和舊版中，如果 省略@provider ， SQLNCLI 則會使用。使用 SQLNCLI 會將 SQL Server 重新導向至最新版的 SQL Server Native Client OLE DB 提供者。 OLE DB 提供者預期會登錄在登錄中指定的 PROGID。建議不要SQLNCLI使用 MSOLEDBSQL
從 SQL Server 2022 （16.x）開始，您必須指定提供者名稱。建議使用MSOLEDBSQL 。如果您省略 @provider，您可能會遇到非預期的行為。

重要

SQL Server Native Client (通常縮寫為 SNAC) 已從 SQL Server 2022 (16.x) 和 SQL Server Management Studio 19 (SSMS) 中移除。不建議使用 SQL Server Native Client OLE DB 提供者 (SQLNCLI 或 SQLNCLI11) 和舊版 Microsoft OLE DB Provider for SQL Server (SQLOLEDB) 進行新的開發。請切換至新的 Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server 以繼續使用。

[ @datasrc = ] N'datasrc'

數據源的名稱，如 OLE DB 提供者所解譯。 @datasrc為 nvarchar（4000），預設值為 NULL。 @datasrc會當做 DBPROP_INIT_DATASOURCE 屬性傳遞，以初始化 OLE DB 提供者。

[ @location = ] N'location'

OLE DB 提供者所解譯的資料庫位置。 @location為 nvarchar（4000），預設值為 NULL。 @location會當做 DBPROP_INIT_LOCATION 屬性傳遞，以初始化 OLE DB 提供者。

[ @provstr = ] N'provstr'

識別唯一數據源的 OLE DB 提供者特定連接字串。 @provstr為 nvarchar（4000），預設值為 NULL。自變數 provstr 會傳遞至 IDataInitialize 或設定為 DBPROP_INIT_PROVIDERSTRING 屬性，以初始化 OLE DB 提供者。

針對 SQL Server Native Client OLE DB 提供者建立連結的伺服器時，可以使用關鍵詞指定 SERVER=servername\instancename SQL Server 的特定實例來指定SERVER實例。 servername 是執行 SQL Server 的計算機名稱，而 instancename 是使用者所連線之 SQL Server 特定實例的名稱。

若要存取鏡像資料庫，連接字串必須包含資料庫名稱。需要此名稱，才能啟用數據存取提供者的故障轉移嘗試。您可以在 @provstr 或 @catalog 參數中指定資料庫。或者，連接字串也可以提供故障轉移夥伴名稱。

如果您sp_addlinkedserver從本機登入執行，或不是系統管理員角色一部分的登入，您可能會收到下列錯誤：

Access to the remote server is denied because no login-mapping exists.

若要解決此問題，請將參數新增User ID至您的連接字串。在下列範例中，myUser是傳遞至連接字串的使用者標識碼：

EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName',
    @provider = N'SQLNCLI',
    @srvproduct = 'MS SQL Server',
    @provstr = N'SERVER=serverName\InstanceName;User ID=myUser'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName',
    @locallogin = NULL,
    @useself = N'False',
    @rmtuser = N'myUser',
    @rmtpassword = N'*****'

如需詳細資訊，請參閱拒絕存取遠端伺服器，因為沒有任何登入對應存在。

[ @catalog = ] N'catalog'

對 OLE DB 提供者建立連接時要使用的目錄。 @catalog為 sysname，預設值為 NULL。 @catalog會當做 DBPROP_INIT_CATALOG 屬性傳遞，以初始化 OLE DB 提供者。針對 SQL Server 實例定義連結的伺服器時，目錄會參考連結伺服器所對應的預設資料庫。

[ @linkedstyle = ] linkedstyle

僅供參考之用。不支援。我們無法保證未來的相容性。

傳回碼值

0 (成功) 或 1 (失敗)。

結果集

無。

備註

下表顯示連結伺服器可以針對可透過 OLE DB 存取的數據源設定的方式。鏈接的伺服器可以針對特定數據源設定多個方式;數據源類型可以有多個數據列。下表也會顯示 sp_addlinkedserver 要用於設定連結伺服器的參數值。

遠端 OLE DB 資料源	OLE DB 提供者	@srvproduct	@provider	@datasrc	@provstr	@catalog
SQL Server	SQL Server Native Client OLE DB 提供者	SQL Server ¹ （預設值）
SQL Server	SQL Server Native Client OLE DB 提供者		`SQLNCLI`	SQL Server 的網路名稱（適用於預設實例）		資料庫名稱 (選擇性)
SQL Server	SQL Server Native Client OLE DB 提供者		`SQLNCLI`	servername\實例名稱（針對特定實例）		資料庫名稱 (選擇性)
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 資料庫的目錄名稱

¹ 這種設定連結伺服器的方式會強制連結伺服器的名稱與 SQL Server 遠端實例的網路名稱相同。使用 @datasrc 來指定伺服器。

² 「任何」表示產品名稱可以是任何專案。

SQL Server Native Client OLE DB 提供者是未指定任何提供者名稱或 SQL Server 指定為產品名稱時，與 SQL Server 搭配使用的提供者。即使您指定較舊的提供者名稱 SQLOLEDB，它也會在保存至目錄時變更為 SQLNCLI。

@datasrc、@location、@provstr和@catalog參數會識別連結伺服器所指向的資料庫或資料庫。如果其中任何一個參數為 NULL，則不會設定對應的 OLE DB 初始化屬性。

在叢集環境中，當您指定要指向 OLE DB 數據源的檔名時，請使用通用命名慣例名稱（UNC）或共用磁碟驅動器來指定位置。

預存程式 sp_addlinkedserver 無法在使用者定義的交易內執行。

重要

Azure SQL 受控執行個體目前僅支援 SQL Server、SQL 資料庫和其他 SQL 受控實例作為遠端數據源。

重要

使用 sp_addlinkedserver建立連結的伺服器時，會為所有本機登入新增預設的自我對應。若為非 SQL Server 提供者，SQL Server 已驗證的登入可能無法存取 SQL Server 服務帳戶下的提供者。系統管理員應該考慮使用 sp_droplinkedsrvlogin <linkedserver_name>, NULL 來移除全域對應。

權限

語句 sp_addlinkedserver 需要 ALTER ANY LINKED SERVER 許可權。（SQL Server Management Studio [新增連結的伺服器] 對話方塊是以需要系統管理員固定伺服器角色成員資格的方式實作。

範例

A. 使用 Microsoft SQL Server OLE DB 提供者

下列範例會建立名為 SEATTLESales的鏈接伺服器。產品名稱為 SQL Server，且未使用任何提供者名稱。

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

下列範例會使用 SQL Server OLE DB 驅動程式，在 SQL Server 實例上建立連結的伺服器 S1_instance1 。

EXEC sp_addlinkedserver
   @server=N'S1_instance1',
   @srvproduct=N'',
   @provider=N'MSOLEDBSQL',
   @datasrc=N'S1\instance1';

下列範例會使用 SQL Server Native Client OLE DB 提供者，在 SQL Server 實例上建立連結的伺服器 S1_instance1 。

重要

SQL Server Native Client OLE DB 提供者（SQLNCLI）仍然已被取代，不建議將其用於新的開發工作。請改為使用新的 Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)，其會進行更新且具備最新的伺服器功能。

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 格式的 access 資料庫Microsoft。下列範例會建立名為 SEATTLE Mktg的鏈接伺服器。

注意

此範例假設已安裝 Microsoft Access 和範例 Northwind 資料庫，而且 Northwind 資料庫位於與 SQL Server 實例相同的伺服器上 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

C. 搭配參數使用 Microsoft OLE DB Provider for ODBC `datasrc`

下列範例會建立名為 SEATTLE Payroll 的鏈接伺服器，其使用 Microsoft OLE DB Provider for ODBC （MSDASQL）和 @datasrc 參數。

注意

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

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

D. 使用 Microsoft OLE DB Provider for 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';

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

下列範例會建立連結的伺服器來直接存取文本檔，而不需將檔案連結為 Access .mdb 檔案中的數據表。提供者為 Microsoft.Jet.OLEDB.4.0 ，而提供者字串為 Text。

數據來源是包含文字檔之目錄的完整路徑。描述文字文件結構的schema.ini檔案必須存在於與文本檔相同的目錄中。如需如何建立schema.ini檔案的詳細資訊，請參閱 Jet 資料庫引擎檔。

首先，建立鏈接的伺服器。

EXEC sp_addlinkedserver txtsrv, N'Jet 4.0',
   N'Microsoft.Jet.OLEDB.4.0',
   N'c:\data\distqry',
   NULL,
   N'Text';

設定登入對應。

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL;

列出連結伺服器中的數據表。

EXEC sp_tables_ex txtsrv;

在此案例 file1#txt中，使用四部分名稱查詢其中一個數據表。

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. 將 Azure SQL 資料庫新增為連結伺服器，以搭配雲端和內部部署資料庫的分散式查詢使用

您可以將 Azure SQL 資料庫新增為連結的伺服器，然後將它與橫跨內部部署和雲端資料庫的分散式查詢搭配使用。這是跨內部部署公司網路和 Azure 雲端的資料庫混合式解決方案的元件。

SQL Server 方塊產品包含分散式查詢功能，可讓您撰寫查詢，以結合本機數據源的數據，以及來自遠端來源的數據（包括非 SQL Server 數據源的數據）定義為連結的伺服器。每個 Azure SQL 資料庫（邏輯伺服器 master 的資料庫除外）都可以新增為個別連結的伺服器，然後直接在資料庫應用程式中作為任何其他資料庫使用。

使用 Azure SQL 資料庫的優點包括可管理性、高可用性、延展性、使用熟悉的開發模型，以及關係型數據模型。資料庫應用程式的需求會決定如何在雲端中使用 Azure SQL 資料庫。您可以將所有數據一次移至 Azure SQL 資料庫，或逐漸移動部分數據，同時保留其餘內部部署數據。對於這類混合式資料庫應用程式，Azure SQL 資料庫現在可以新增為連結的伺服器，而資料庫應用程式可以發出分散式查詢，以結合來自 Azure SQL 資料庫和內部部署數據源的數據。

以下範例說明如何使用分散式查詢連線到 Azure SQL 資料庫。

首先，使用 SQL Server Native Client，將一個 Azure SQL 資料庫新增為連結的伺服器。

EXEC sp_addlinkedserver
    @server = 'LinkedServerName',
    @srvproduct = '',
    @provider = 'sqlncli',
    @datasrc = 'ServerName.database.windows.net',
    @location = '',
    @provstr = '',
    @catalog = 'DatabaseName';

將認證和選項新增至此連結的伺服器。

EXEC sp_addlinkedsrvlogin
  @rmtsrvname = 'LinkedServerName',
  @useself = 'false',
  @rmtuser = 'LoginName',
  @rmtpassword = 'myPassword';

EXEC sp_serveroption 'LinkedServerName', 'rpc out', true;

現在，使用鏈接的伺服器使用四部分名稱來執行查詢，甚至建立新的數據表並插入數據。

EXEC ('CREATE TABLE SchemaName.TableName(col1 int not null CONSTRAINT PK_col1 PRIMARY KEY CLUSTERED (col1) )') at LinkedServerName;
EXEC ('INSERT INTO SchemaName.TableName VALUES(1),(2),(3)') at LinkedServerName;

使用四部分名稱查詢資料：

SELECT * FROM LinkedServerName.DatabaseName.SchemaName.TableName;

H. 使用受控識別驗證建立 Azure SQL 受控執行個體連結的伺服器

注意

Microsoft Entra ID 先前稱為 Azure Active Directory (Azure AD)。

若要建立具有受控識別驗證的鏈接伺服器，請執行下列 T-SQL，並將取代 <managed_instance> 為您自己的 SQL 受控實例。驗證方法ActiveDirectoryMSI會在 @provstr 參數中使用。請考慮選擇性地使用 @locallogin = NULL 來允許所有本機登入。

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyLinkedServer',
    @srvproduct = N'',
    @provider = N'MSOLEDBSQL',
    @provstr = N'Server=<mi_name>.<dns_zone>.database.windows.net,1433;Authentication=ActiveDirectoryMSI;';

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname = N'MyLinkedServer',
    @useself = N'False',
    @locallogin = N'user1@contoso.com';

若要使用受控識別啟用驗證，指派給 Azure SQL 受控執行個體的受控識別必須新增為遠端受控實例的登入。支援系統指派和使用者指派的受控識別。

如果已設定主要身分識別，則會使用它，否則會使用系統指派的受控識別。如果受控識別以相同名稱重新建立，則必須重新建立遠端實例上的登入，因為新的受控識別應用程式標識碼和 SQL 受管理執行個體服務主體 SID 不再相符。若要確認這兩個值相符，請使用下列查詢，將 SID 轉換為應用程式識別碼。

SELECT convert(uniqueidentifier, sid) as MSEntraApplicationID
FROM sys.server_principals
WHERE name = '<managed_instance_name>';

I. 建立具有傳遞 Microsoft Entra 驗證的 SQL 受控執行個體連結的伺服器

若要建立具有傳遞驗證的連結伺服器，請執行下列 T-SQL，並將取代 <managed_instance> 為您自己的 SQL 受控實例伺服器：

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyLinkedServer',
    @srvproduct = N'',
    @provider = N'MSOLEDBSQL',
    @datasrc = N'<mi_name>.<dns_zone>.database.windows.net,1433';

透過傳遞驗證，本機登入的安全性內容會傳遞至遠端實例。傳遞驗證需要將 Microsoft Entra 主體新增為本機和遠端 Azure SQL 受控執行個體的登入。這兩個受控實例都必須位於伺服器信任群組中。符合需求時，使用者可以登入本機實例，並透過連結的伺服器對象查詢遠端實例。

分享方式：

sp_addlinkedserver (Transact-SQL)

語法

引數

[ @server = ] N'server'

[ @srvproduct = ] N'srvproduct'

[ @provider = ] N'provider'

[ @datasrc = ] N'datasrc'

[ @location = ] N'location'

[ @provstr = ] N'provstr'

[ @catalog = ] N'catalog'

[ @linkedstyle = ] linkedstyle

傳回碼值

結果集

備註

權限

範例

A. 使用 Microsoft SQL Server OLE DB 提供者

B. 使用 Microsoft OLE DB Provider for Microsoft Access

C. 搭配參數使用 Microsoft OLE DB Provider for ODBC `datasrc`

D. 使用 Microsoft OLE DB Provider for Excel 電子表格

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

F. 使用 Microsoft OLE DB Provider for DB2

G. 將 Azure SQL 資料庫新增為連結伺服器，以搭配雲端和內部部署資料庫的分散式查詢使用

H. 使用受控識別驗證建立 Azure SQL 受控執行個體連結的伺服器

I. 建立具有傳遞 Microsoft Entra 驗證的 SQL 受控執行個體連結的伺服器

意見反映

更多資源

分享方式：

sp_addlinkedserver (Transact-SQL)

語法

引數

[ @server = ] N'server'

[ @srvproduct = ] N'srvproduct'

[ @provider = ] N'provider'

[ @datasrc = ] N'datasrc'

[ @location = ] N'location'

[ @provstr = ] N'provstr'

[ @catalog = ] N'catalog'

[ @linkedstyle = ] linkedstyle

傳回碼值

結果集

備註

權限

範例

A. 使用 Microsoft SQL Server OLE DB 提供者

B. 使用 Microsoft OLE DB Provider for Microsoft Access

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

D. 使用 Microsoft OLE DB Provider for Excel 電子表格

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

F. 使用 Microsoft OLE DB Provider for DB2

G. 將 Azure SQL 資料庫新增為連結伺服器，以搭配雲端和內部部署資料庫的分散式查詢使用

H. 使用受控識別驗證建立 Azure SQL 受控執行個體 連結的伺服器

I. 建立具有傳遞 Microsoft Entra 驗證的 SQL 受控執行個體連結的伺服器

相關內容

意見反映

更多資源

C. 搭配參數使用 Microsoft OLE DB Provider for ODBC `datasrc`

H. 使用受控識別驗證建立 Azure SQL 受控執行個體連結的伺服器