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 在注册表中注册。 SQLNCLIMSOLEDBSQL建议不要这样做。
从 SQL Server 2022（16.x）开始，必须指定提供程序名称。建议使用MSOLEDBSQL 。如果省略 @provider，可能会遇到意外行为。

重要

已从 SQL Server 2022 (16.x) 和 SQL Server Management Studio 19 (SSMS) 中移除 SQL Server Native Client（通常缩写为 SNAC）。不建议在新的开发工作中使用 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指定SERVER实例来指定 SQL Server 的特定实例。 servername 是运行 SQL Server 的计算机的名称，实例名称是用户要连接到的特定 SQL Server 实例的名称。

若要访问镜像数据库，则连接字符串必须包含数据库名称。该名称是数据访问接口启用故障转移尝试所必需的。可以在@provstr或@catalog参数中指定数据库。此外，连接字符串还可以提供故障转移伙伴名称。

如果从本地登录名运行sp_addlinkedserver，或者不是 sysadmin 角色的一部分的登录名，可能会收到以下错误：

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

若要解决此问题，请将User ID参数添加到连接字符串。在以下示例中，myUser是传递给连接字符串的用户 ID：

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\instancename （针对特定实例）		“数据库名称”（可选）
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 提供程序	任意	`MSIDXS`	索引服务目录名称
Microsoft Excel 电子表格	Microsoft OLE DB Provider for Jet	任意	`Microsoft.Jet.OLEDB.4.0`	Excel 文件的完整路径	Excel 5.0
IBM Db2 数据库	用于 DB2 的 Microsoft OLE DB 提供程序	任意	`DB2OLEDB`		请参阅DB2 的 Microsoft OLE DB 提供程序文档。	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 以需要 sysadmin 固定服务器角色成员身份的方式实现新的链接服务器对话框。

示例

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 访问接口进行Microsoft访问

Microsoft.Jet.OLEDB.4.0 访问接口连接到使用 2002-2003 格式的 Microsoft Access 数据库。下面的示例将创建一个名为 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. 使用适用于 Jet 的 Microsoft OLE DB 提供程序访问文本文件

以下示例创建直接访问文本文件的链接服务器，而没有将这些文件链接为 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. 使用DB2 的 Microsoft OLE DB 提供程序

以下示例创建一个使用DB2DB2 的 Microsoft OLE DB 提供程序的链接服务器。

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 Box 产品包含分布式查询功能，可用于编写查询，以合并本地数据源中的数据和远程源（包括非 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 托管实例。身份验证方法在@provstr参数中使用ActiveDirectoryMSI。请考虑选择使用 @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 托管实例的托管标识添加为远程托管实例的登录名。系统分配的托管标识和用户分配的托管标识均受支持。

如果设置了主标识，则使用它，否则使用系统分配的托管标识。如果使用同名重新创建托管标识，则还需要重新创建远程实例上的登录名，因为新的托管标识应用程序 ID 和SQL 托管实例服务主体 SID 不再匹配。若要验证这两个值是否匹配，请使用以下查询将 SID 转换为应用程序 ID。

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 访问接口进行Microsoft访问

°C 将 Microsoft OLE DB Provider for ODBC 与参数配合使用`datasrc`

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

E. 使用适用于 Jet 的 Microsoft OLE DB 提供程序访问文本文件

F. 使用DB2 的 Microsoft OLE DB 提供程序

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 访问接口进行Microsoft访问

°C 将 Microsoft OLE DB Provider for ODBC 与参数配合使用datasrc

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

E. 使用适用于 Jet 的 Microsoft OLE DB 提供程序访问文本文件

F. 使用DB2 的 Microsoft OLE DB 提供程序

G. 将 Azure SQL 数据库添加为链接服务器，以便与云和本地数据库上的分布式查询配合使用

H. 使用托管标识身份验证创建Azure SQL 托管实例链接服务器

I. 创建使用直通 Microsoft Entra 身份验证的 SQL 托管实例链接服务器

相关内容

反馈

其他资源

°C 将 Microsoft OLE DB Provider for ODBC 与参数配合使用`datasrc`