sp_addlinkedserver (Transact-SQL)

适用于: SQL Server(所有受支持的版本) Azure SQL 托管实例

创建链接服务器。 链接服务器让用户可以对 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'

要创建的链接服务器的名称。 参数 服务器sysname,没有默认值。

[ @srvproduct = ] 'product_name'

要添加为链接服务器的 OLE DB 数据源的产品名称。 product_name值为 nvarchar (128) ,默认值为 NULL。 如果值为SQL Server,则无需指定provider_namedata_source位置provider_string目录

[ @provider = ] 'provider_name'

与此数据源对应的 OLE DB 访问接口的唯一编程标识符 (PROGID)。 对于当前计算机上安装的指定 OLE DB 访问接口, provider_name 必须是唯一的。 provider_name值为 nvarchar (128)

  • 在 SQL Server 2022 (16.x) 之前,如果@provider省略 ,则使用 SQLNCLI。 使用 SQLNCLI 会将 SQL Server 重定向至 SQL Server Native Client OLE DB 提供程序的最新版本。 OLE DB 提供程序应以指定的 PROGID 在注册表中注册。 建议使用 MSOLEDBSQL 而不是 SQLNCLI。
  • 从 SQL Server 2022 (16.x) 开始,必须指定提供程序名称。 建议使用 MSOLEDBSQL。

重要

SQL Server Native Client通常缩写为 SNAC) (已从 2022 SQL Server (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 = ] 'data_source'

由 OLE DB 访问接口解释的数据源的名称。 data_source值为 nvarchar (4000) data_source 作为 DBPROP_INIT_DATASOURCE 属性传递,用于初始化 OLE DB 访问接口。

[ @location = ] 'location'

由 OLE DB 访问接口解释的数据库的位置。 值 位置nvarchar (4000) ,默认值为 NULL。 参数 位置 作为 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 访问接口建立连接时所使用的目录。 值 目录sysname,默认值为 NULL。 参数 目录 作为 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,版本 8 及更高版本 Oracle Provider for OLE DB Any OraOLEDB.Oracle 用于 Oracle 数据库的别名
Access/Jet Microsoft OLE DB Provider for Jet Any 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 Any DB2OLEDB 请参阅DB2 的 Microsoft OLE DB 提供程序文档。 DB2 数据库的目录名称

1 这种设置链接服务器的方式强制链接服务器的名称与 SQL Server 远程实例的网络名称相同。 使用 data_source 指定服务器。

2 “Any”表示产品名称可以是任何内容。

如果未指定提供程序名称或指定SQL Server作为产品名称,则 Microsoft SQL Server Native Client OLE DB 提供程序是与 SQL Server 一起使用的提供程序。 即使指定了较早版本的访问接口名称 SQLOLEDB,在保存到目录时该名称也将改为 SQLNCLI。

data_source位置provider_string目录参数标识链接服务器指向的数据库。 如果其中任一参数为 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 Provider for Microsoft Access

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 与 data_source 参数一起使用

以下示例创建一个名为 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. 使用 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. 使用DB2 的 Microsoft OLE DB 提供程序

以下示例创建名为 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 box 产品包含分布式查询功能,使你可以编写查询以合并来自本地数据源的数据和来自远程源的数据, (包括非SQL Server数据源的数据) 定义为链接服务器的数据。 除了逻辑服务器master的数据库) 之外,每个Azure SQL数据库 (都可以添加为单独的链接服务器,然后作为任何其他数据库直接在数据库应用程序中使用。

使用 Azure SQL 数据库的好处包括可管理性、高可用性、可伸缩性、使用熟悉的开发模型和关系数据模型。 数据库应用程序的要求决定了它在云中如何使用 Azure SQL Database。 可以一次性将所有数据移动到数据库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 AD 身份验证的 SQL 托管实例链接服务器

若要创建具有托管标识身份验证的链接服务器,请执行以下 T-SQL。 身份验证方法在 参数中使用ActiveDirectoryMSI@provstr。 (可选)考虑使用 @locallogin = NULL 以允许所有本地登录名。

EXEC master.dbo.sp_addlinkedserver
@server     = N'MyLinkedServer',
@srvproduct = N'',
@provider   = N'MSOLEDBSQL',
@provstr    = N'Server=mi.35e5bd1a0e9b.database.windows.net,1433;Authentication=ActiveDirectoryMSI;';

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

如果Azure SQL 托管实例托管标识 (以前称为托管服务标识) 作为登录名添加到远程托管实例,则可以使用上一示例中创建的链接服务器进行托管标识身份验证。 支持系统分配的托管标识和用户分配的托管标识。

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

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

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

若要创建具有直通身份验证的链接服务器,请执行 T-SQL。

EXEC master.dbo.sp_addlinkedserver
@server     = N'MyLinkedServer',
@srvproduct = N'',
@provider   = N'MSOLEDBSQL',
@datasrc    = N'mi.35e5bd1a0e9b.database.windows.net,1433';

使用直通身份验证,本地登录的安全上下文将传递到远程实例。 直通身份验证要求将 AAD 主体添加为本地和远程Azure SQL 托管实例登录名。 这两个托管实例都需要在 服务器信任组中。 满足要求后,用户可以登录到本地实例,并通过链接服务器对象查询远程实例。

请参阅