sp_addlinkedserver (Transact-SQL)

项目
03/07/2014

创建链接服务器。链接服务器让用户可以对 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)。对于当前计算机中安装的指定 OLE DB 访问接口，provider_name 必须唯一。 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 作为 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 访问接口建立连接时所使用的目录。 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	目录
SQL Server	Microsoft SQL Server Native Client OLE DB 访问接口	SQL Server1（默认值）
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	任何	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 访问接口将是用于 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 访问接口

下面的示例将创建一个名为 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.将 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 用于 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';

若要连接到 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 数据库引擎文档。

--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 Database 作为链接服务器以用于云数据库和本地数据库上的分布式查询

您可以添加一个 Windows Azure SQL Database 作为链接服务器，然后将它用于跨本地数据库和云数据库的分布式查询。这是用于跨本地公司网络和 Windows Azure 云的数据库混合解决方案的组件。

SQL Server 现成产品包含分布式查询功能，这允许您编写查询来合并定义为链接服务器的本地数据源的数据和远程数据源的数据（包括来自非 SQL Server 数据源的数据）。每个 Windows Azure SQL Database（虚拟 master 除外）可以添加为单个链接服务器，然后在您的数据库应用程序中像任何其他数据库一样直接使用它。

使用 Windows Azure SQL Database 的好处包括便于管理、高可用性和可伸缩性、使用熟悉的开发模型以及采用关系数据模型。您的数据库应用程序要求决定了它将在云中如何使用 Windows Azure SQL Database。您可以立即将所有数据移到 Windows Azure SQL Database，或逐渐迁移一部分数据而将其余数据保留在本地。对于这类混合数据库应用程序，Windows Azure SQL Database 现在可以作为链接服务器添加且数据库应用程序可以发布分布式查询命令来合并 Windows Azure SQL Database 和本地数据源上的数据。

以下的简单示例说明了如何使用分布式查询连接到 Windows Azure SQL Database：

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