sp_addlinkedserver (Transact-SQL)sp_addlinkedserver (Transact-SQL)

适用对象:yesSQL Server(从 2008 版开始)noAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server (starting with 2008) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

创建链接服务器。Creates a linked server. 链接服务器让用户可以对 OLE DB 数据源进行分布式异类查询。A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. 使用创建链接的服务器后sp_addlinkedserver、 分布式查询可以对该服务器运行。After a linked server is created by using sp_addlinkedserver, distributed queries can be run against this server. 如果链接服务器定义为 SQL ServerSQL Server实例,则可执行远程存储过程。If the linked server is defined as an instance of SQL ServerSQL Server, remote stored procedures can be executed.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax


sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]   
     [ , [ @provider= ] 'provider_name' ]  
     [ , [ @datasrc= ] 'data_source' ]   
     [ , [ @location= ] 'location' ]   
     [ , [ @provstr= ] 'provider_string' ]   
     [ , [ @catalog= ] 'catalog' ]   

参数Arguments

[ @server= ] 'server'[ @server= ] 'server'
要创建的链接服务器的名称。Is the name of the linked server to create. server 的数据类型为 sysname,无默认值。server is sysname, with no default.

[ @srvproduct= ] **'***product_name*****[ @srvproduct= ] 'product_name'
要添加为链接服务器的 OLE DB 数据源的产品名称。Is the product name of the OLE DB data source to add as a linked server. product_namenvarchar ( 128 ),默认值为 NULL。product_name is nvarchar( 128 ), with a default of NULL. 如果SQL Serverprovider_namedata_source位置provider_string,和目录无需指定。If SQL Server, provider_name, data_source, location, provider_string, and catalog do not have to be specified.

[ @provider= ] **'***provider_name*****[ @provider= ] 'provider_name'
与此数据源对应的 OLE DB 访问接口的唯一编程标识符 (PROGID)。Is the unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to this data source. provider_name必须是唯一的当前计算机上安装的指定 OLE DB 访问接口。provider_name must be unique for the specified OLE DB provider installed on the current computer. provider_namenvarchar ( 128 ),默认值为 NULL; 但是,如果provider_name是省略,则使用 SQLNCLI。provider_name is nvarchar( 128 ), with a default of NULL; however, if provider_name is omitted, SQLNCLI is used. (使用 SQLNCLI 并且 SQL ServerSQL Server 将重定向到 SQL ServerSQL Server Native Client OLE DB 访问接口的最新版本。)OLE DB 访问接口应以指定的 PROGID 在注册表中注册。(Use SQLNCLI and SQL ServerSQL Server will redirect to the latest version of SQL ServerSQL Server Native Client OLE DB Provider.) The OLE DB provider is expected to be registered with the specified PROGID in the registry.

[ @datasrc= ] **'***data_source*****[ @datasrc= ] 'data_source'
由 OLE DB 访问接口解释的数据源的名称。Is the name of the data source as interpreted by the OLE DB provider. data_sourcenvarchar ( 4000 )data_source is nvarchar( 4000 ). data_source作为以初始化 OLE DB 访问接口的 DBPROP_INIT_DATASOURCE 属性传递。data_source is passed as the DBPROP_INIT_DATASOURCE property to initialize the OLE DB provider.

[ @location= ] **'***位置*****[ @location= ] 'location'
由 OLE DB 访问接口解释的数据库的位置。Is the location of the database as interpreted by the OLE DB provider. 位置nvarchar ( 4000 ),默认值为 NULL。location is nvarchar( 4000 ), with a default of NULL. 位置作为 DBPROP_INIT_LOCATION 属性传递以初始化 OLE DB 访问接口。location is passed as the DBPROP_INIT_LOCATION property to initialize the OLE DB provider.

[ @provstr= ] **'***provider_string*****[ @provstr= ] 'provider_string'
OLE DB 访问接口特定的连接字符串,它可标识唯一的数据源。Is the OLE DB provider-specific connection string that identifies a unique data source. provider_stringnvarchar ( 4000 ),默认值为 NULL。provider_string is nvarchar( 4000 ), with a default of NULL. provstr传递给 IDataInitialize,或者设置为 DBPROP_INIT_PROVIDERSTRING 属性以初始化 OLE DB 访问接口。provstr is either passed to IDataInitialize or set as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider.

当针对创建链接的服务器 SQL ServerSQL ServerNative Client OLE DB 提供程序实例可以通过为服务器使用 SERVER 关键字指定 =servername\instancename指定的特定实例 SQL ServerSQL ServerWhen the linked server is created against the SQL ServerSQL Server Native Client OLE DB provider, the instance can be specified by using the SERVER keyword as SERVER=servername\instancename to specify a specific instance of SQL ServerSQL Server. servername所在的计算机的名称 SQL ServerSQL Server正在运行,并instancename的特定实例的名称 SQL ServerSQL Server用户将连接到。servername is the name of the computer on which SQL ServerSQL Server is running, and instancename is the name of the specific instance of SQL ServerSQL Server to which the user will be connected.

备注

若要访问镜像数据库,则连接字符串必须包含数据库名称。To access a mirrored database, a connection string must contain the database name. 该名称是数据访问接口启用故障转移尝试所必需的。This name is necessary to enable failover attempts by the data access provider. 可以在指定的数据库@provstr@catalog参数。The database can be specified in the @provstr or @catalog parameter. 此外,连接字符串还可以提供故障转移伙伴名称。Optionally, the connection string can also supply a failover partner name.

[ @catalog= ] **'***目录*****[ @catalog= ] 'catalog'
与 OLE DB 访问接口建立连接时所使用的目录。Is the catalog to be used when a connection is made to the OLE DB provider. 目录sysname,默认值为 NULL。catalog is sysname, with a default of NULL. 目录作为 DBPROP_INIT_CATALOG 属性传入以初始化 OLE DB 访问接口进行传递。catalog is passed as the DBPROP_INIT_CATALOG property to initialize the OLE DB provider. 在针对 SQL ServerSQL Server 实例定义链接服务器时,目录指向链接服务器映射到的默认数据库。When the linked server is defined against an instance of SQL ServerSQL Server, catalog refers to the default database to which the linked server is mapped.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

结果集Result Sets

无。None.

备注Remarks

下表显示为能通过 OLE DB 访问数据源而建立链接服务器的方法。The following table shows the ways that a linked server can be set up for data sources that can be accessed through OLE DB. 对于特定的数据源,可以使用多种方法为其设置链接服务器;该表中可能有多行适用于一种数据源类型。A linked server can be set up more than one way for a particular data source; there can be more than one row for a data source type. 此表还显示sp_addlinkedserver设置链接服务器使用的参数值。This table also shows the sp_addlinkedserver parameter values to be used for setting up the linked server.

远程 OLE DB 数据源Remote OLE DB data source OLE DB 访问接口OLE DB provider product_nameproduct_name provider_nameprovider_name data_sourcedata_source 位置location provider_stringprovider_string 目录catalog
SQL ServerSQL Server MicrosoftMicrosoft SQL ServerSQL Server Native Client OLE DB 访问接口 SQL ServerSQL Server Native Client OLE DB Provider SQL ServerSQL Server 1 (默认值) 1 (default)
SQL ServerSQL Server MicrosoftMicrosoft SQL ServerSQL Server Native Client OLE DB 访问接口 SQL ServerSQL Server Native Client OLE DB Provider SQLNCLISQLNCLI SQL ServerSQL Server 的网络名称(用于默认实例)Network name of SQL ServerSQL Server (for default instance) 数据库名称(可选)Database name (optional)
SQL ServerSQL Server MicrosoftMicrosoft SQL ServerSQL Server Native Client OLE DB 访问接口 SQL ServerSQL Server Native Client OLE DB Provider SQLNCLISQLNCLI servername\instancename (用于特定实例)servername\instancename (for specific instance) 数据库名称(可选)Database name (optional)
Oracle,版本 8 及更高版本Oracle, version 8 and later Oracle Provider for OLE DBOracle Provider for OLE DB AnyAny 接口 OraOLEDB.OracleOraOLEDB.Oracle 用于 Oracle 数据库的别名Alias for the Oracle database
Access/JetAccess/Jet Microsoft OLE DB Provider for JetMicrosoft OLE DB Provider for Jet AnyAny Microsoft.Jet.OLEDB.4.0Microsoft.Jet.OLEDB.4.0 Jet 数据库文件的完整路径Full path of Jet database file
ODBC 数据源ODBC data source Microsoft OLE DB Provider for ODBCMicrosoft OLE DB Provider for ODBC AnyAny MSDASQLMSDASQL ODBC 数据源的系统 DSNSystem DSN of ODBC data source
ODBC 数据源ODBC data source MicrosoftMicrosoft OLE DB Provider for ODBC OLE DB Provider for ODBC AnyAny MSDASQLMSDASQL ODBC 连接字符串ODBC connection string
文件系统File system MicrosoftMicrosoft OLE DB Provider for Indexing Service OLE DB Provider for Indexing Service AnyAny MSIDXSMSIDXS 索引服务目录名称Indexing Service catalog name
MicrosoftMicrosoft Excel 电子表格 Excel Spreadsheet MicrosoftMicrosoft OLE DB Provider for Jet OLE DB Provider for Jet AnyAny Microsoft.Jet.OLEDB.4.0Microsoft.Jet.OLEDB.4.0 Excel 文件的完整路径Full path of Excel file Excel 5.0Excel 5.0
IBM DB2 数据库IBM DB2 Database MicrosoftMicrosoft OLE DB Provider for DB2 OLE DB Provider for DB2 AnyAny DB2OLEDBDB2OLEDB 请参阅 MicrosoftMicrosoftOLE DB Provider for DB2 文档。See MicrosoftMicrosoft OLE DB Provider for DB2 documentation. DB2 数据库的目录名称Catalog name of DB2 database

1设置链接服务器的这种方式强制链接服务器的远程实例的网络名称相同的名称 SQL ServerSQL Server1 This way of setting up a linked server forces the name of the linked server to be the same as the network name of the remote instance of SQL ServerSQL Server. 使用data_source以指定的服务器。Use data_source to specify the server.

2 "任何"指产品名称可以是任何内容。2 "Any" indicates that the product name can be anything.

MicrosoftMicrosoft SQL ServerSQL Server Native Client OLE DB 提供程序是与一起使用的提供程序 SQL ServerSQL Server如果未不指定任何提供程序名称,或如果 SQL ServerSQL Server被指定为产品名称。The MicrosoftMicrosoft SQL ServerSQL Server Native Client OLE DB provider is the provider that is used with SQL ServerSQL Server if no provider name is specified or if SQL ServerSQL Server is specified as the product name. 即使指定了较早版本的访问接口名称 SQLOLEDB,在保存到目录时该名称也将改为 SQLNCLI。Even if you specify the older provider name, SQLOLEDB, it will be changed to SQLNCLI when persisted to the catalog.

Data_source位置provider_string,以及目录参数标识链接的数据库服务器到点。The data_source, location, provider_string, and catalog parameters identify the database or databases the linked server points to. 如果其中任一参数为 NULL,则不设置相应的 OLE DB 初始化属性。If any one of these parameters is NULL, the corresponding OLE DB initialization property is not set.

在群集环境中,当指定指向 OLE DB 数据源的文件名时,应使用通用命名规则 (UNC) 名称或共享驱动器指定位置。In a clustered environment, when you specify file names to point to OLE DB data sources, use the universal naming convention name (UNC) or a shared drive to specify the location.

sp_addlinkedserver不能在用户定义的事务内执行。sp_addlinkedserver cannot be executed within a user-defined transaction.

重要

通过创建链接的服务器时sp_addlinkedserver,为所有本地登录名添加默认自映射。When a linked server is created by using sp_addlinkedserver, a default self-mapping is added for all local logins. 对于非 SQL ServerSQL Server 访问接口,通过 SQL ServerSQL Server 验证的登录名也许能够以 SQL ServerSQL Server 服务帐户身份访问该访问接口。For non- SQL ServerSQL Server providers, SQL ServerSQL Server Authenticated logins may be able to gain access to the provider under the SQL ServerSQL Server service account. 管理员应考虑使用 sp_droplinkedsrvlogin <linkedserver_name>, NULL 删除全局映射。Administrators should consider using sp_droplinkedsrvlogin <linkedserver_name>, NULL to remove the global mapping.

PermissionsPermissions

sp_addlinkedserver语句需要ALTER ANY LINKED SERVER权限。The sp_addlinkedserver statement requires the ALTER ANY LINKED SERVER permission. (SSMS新建链接服务器要求的成员身份的方式实现对话框sysadmin固定的服务器角色。)(The SSMS New Linked Server dialog box is implemented in a way that requires membership in the sysadmin fixed server role.)

示例Examples

A.A. 使用 Microsoft SQL Server Native Client OLE DB 访问接口Using the Microsoft SQL Server Native Client OLE DB Provider

下面的示例将创建一个名为 SEATTLESales 的链接服务器。The following example creates a linked server named SEATTLESales. 产品名称为 SQL Server,未使用访问接口名称。The product name is SQL Server, and no provider name is used.

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

下面的示例创建一个链接的服务器S1_instance1的实例上 SQL ServerSQL Server通过使用 SQL ServerSQL ServerNative Client OLE DB 提供程序。The following example creates a linked server S1_instance1 on an instance of SQL ServerSQL Server by using the SQL ServerSQL Server Native Client OLE DB provider.

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

B.B. 使用 Microsoft OLE DB Provider for Microsoft AccessUsing the Microsoft OLE DB Provider for Microsoft Access

Microsoft.Jet.OLEDB.4.0 访问接口连接到使用 2002-2003 格式的 Microsoft Access 数据库。The Microsoft.Jet.OLEDB.4.0 provider connects to Microsoft Access databases that use the 2002-2003 format. 下面的示例将创建一个名为 SEATTLE Mktg 的链接服务器。The following example creates a linked server named SEATTLE Mktg.

备注

此示例假定这两个 MicrosoftMicrosoft访问示例和示例Northwind安装数据库并且Northwind数据库位于 C:\Msoffice\Access\Samples 中。This example assumes that both MicrosoftMicrosoft Access and the sample Northwind database are installed and that the Northwind database resides in 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 数据库。The Microsoft.ACE.OLEDB.12.0 provider connects to Microsoft Access databases that use the 2007 format. 下面的示例将创建一个名为 SEATTLE Mktg 的链接服务器。The following example creates a linked server named SEATTLE Mktg.

备注

此示例假定这两个 MicrosoftMicrosoft访问示例和示例Northwind安装数据库并且Northwind数据库位于 C:\Msoffice\Access\Samples 中。This example assumes that both MicrosoftMicrosoft Access and the sample Northwind database are installed and that the Northwind database resides in 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.C. 将 Microsoft OLE DB Provider for ODBC 与 data_source 参数一起使用Using the Microsoft OLE DB Provider for ODBC with the data_source parameter

下面的示例创建名为的链接的服务器SEATTLE Payroll,它使用 MicrosoftMicrosoftOLE DB Provider for ODBC (MSDASQL) 和data_source参数。The following example creates a linked server named SEATTLE Payroll that uses the MicrosoftMicrosoft OLE DB Provider for ODBC (MSDASQL) and the data_source parameter.

备注

在使用该链接服务器之前,必须在该服务器中将指定的 ODBC 数据源名称定义为系统 DSN。The specified ODBC data source name must be defined as System DSN in the server before you use the linked server.

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

D.D. 将 Microsoft OLE DB Provider 用于 Excel 电子表格Using the Microsoft OLE DB Provider for Excel spreadsheet

若要创建链接的服务器定义使用 MicrosoftMicrosoftOLE DB Provider for Jet 访问 1997年-2003年格式的 Excel 电子表格首先创建一个命名的范围在 Excel 中通过指定要选择的 Excel 工作表的行和列。To create a linked server definition using the MicrosoftMicrosoft OLE DB Provider for Jet to access an Excel spreadsheet in the 1997 - 2003 format, first create a named range in Excel by specifying the columns and rows of the Excel worksheet to select. 这样,可以在分布式查询中将此范围的名称引用为表名称。The name of the range can then be referenced as a table name in a distributed query.

EXEC sp_addlinkedserver 'ExcelSource',  
   'Jet 4.0',  
   'Microsoft.Jet.OLEDB.4.0',  
   'c:\MyData\DistExcl.xls',  
   NULL,  
   'Excel 5.0';  
GO  

若要访问 Excel 电子表格中的数据,请将单元范围与名称相关联。To access data from an Excel spreadsheet, associate a range of cells with a name. 以下查询通过使用先前设置的链接服务器,将指定的命名范围 SalesData 作为表来访问。The following query can be used to access the specified named range SalesData as a table by using the linked server set up previously.

SELECT *  
   FROM ExcelSource...SalesData;  
GO  

如果 SQL ServerSQL Server 在可以访问远程共享的域帐户下运行,则可以使用 UNC 路径来代替映射驱动器。If SQL ServerSQL Server is running under a domain account that has access to a remote share, a UNC path can be used instead of a mapped drive.

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 访问接口。To connect to an Excel spreadsheet in the Excel 2007 format use the ACE provider.

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.E. 使用 Microsoft OLE DB Provider for Jet 访问文本文件Using the Microsoft OLE DB Provider for Jet to access a text file

以下示例创建直接访问文本文件的链接服务器,而没有将这些文件链接为 Access .mdb 文件中的表。The following example creates a linked server for directly accessing text files, without linking the files as tables in an Access .mdb file. 访问接口为 Microsoft.Jet.OLEDB.4.0,访问接口字符串为 TextThe provider is Microsoft.Jet.OLEDB.4.0 and the provider string is Text.

数据源是包含文本文件的目录的完整路径。The data source is the full path of the directory that contains the text files. schema.ini 文件(描述文本文件的结构)必须与此文本文件存在于相同的目录中。A schema.ini file, which describes the structure of the text files, must exist in the same directory as the text files. 有关如何创建 Schema.ini 文件的详细信息,请参阅 Jet 数据库引擎文档。For more information about how to create a Schema.ini file, see the Jet Database Engine documentation.

--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.F. 使用 Microsoft OLE DB Provider for DB2Using the Microsoft OLE DB Provider for DB2

以下示例创建名为 DB2 的链接服务器,该服务器使用 Microsoft OLE DB Provider for DB2The following example creates a linked server named DB2 that uses the 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.G. 添加一个 Azure SQL DatabaseAzure SQL Database 作为链接服务器以用于云数据库和本地数据库上的分布式查询Add a Azure SQL DatabaseAzure SQL Database as a Linked Server For Use With Distributed Queries on Cloud and On-Premise Databases

您可以添加一个 Azure SQL DatabaseAzure SQL Database 作为链接服务器,然后将它用于跨本地数据库和云数据库的分布式查询。You can add a Azure SQL DatabaseAzure SQL Database as a linked server and then use it with distributed queries that span the on-premises and cloud databases. 这是用于跨本地公司网络和 Windows Azure 云的数据库混合解决方案的组件。This is a component for database hybrid solutions spanning on-premises corporate networks and the Windows Azure cloud.

SQL ServerSQL Server 现成产品包含分布式查询功能,这允许您编写查询来合并定义为链接服务器的本地数据源的数据和远程数据源的数据(包括来自非 SQL ServerSQL Server 数据源的数据)。The SQL ServerSQL Server box product contains the distributed query feature, which allows you to write queries to combine data from local data sources and data from remote sources (including data from non- SQL ServerSQL Server data sources) defined as linked servers. 每个 Azure SQL DatabaseAzure SQL Database(虚拟 master 除外)可以添加为单个链接服务器,然后在您的数据库应用程序中像任何其他数据库一样直接使用它。Every Azure SQL DatabaseAzure SQL Database (except the virtual master) can be added as an individual linked server and then used directly in your database applications as any other database.

使用 Azure SQL DatabaseAzure SQL Database 的好处包括便于管理、高可用性和可伸缩性、使用熟悉的开发模型以及采用关系数据模型。The benefits of using Azure SQL DatabaseAzure SQL Database include manageability, high availability, scalability, working with a familiar development model, and a relational data model. 您的数据库应用程序要求决定了它将在云中如何使用 Azure SQL DatabaseAzure SQL DatabaseThe requirements of your database application determine how it would use Azure SQL DatabaseAzure SQL Database in the cloud. 您可以立即将所有数据移到 Azure SQL DatabaseAzure SQL Database,或逐渐迁移一部分数据而将其余数据保留在本地。You can move all of your data at once to Azure SQL DatabaseAzure SQL Database, or progressively move some of your data while keeping the remaining data on-premises. 对于这类混合数据库应用程序, Azure SQL DatabaseAzure SQL Database 现在可以作为链接服务器添加且数据库应用程序可以发布分布式查询命令来合并 Azure SQL DatabaseAzure SQL Database 和本地数据源上的数据。For such a hybrid database application, Azure SQL DatabaseAzure SQL Database can now be added as linked servers and the database application can issue distributed queries to combine data from Azure SQL DatabaseAzure SQL Database and on-premise data sources.

以下的简单示例说明了如何使用分布式查询连接到 Azure SQL DatabaseAzure SQL DatabaseHere’s a simple example explaining how to connect to a Azure SQL DatabaseAzure SQL Database using distributed queries:

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

请参阅See Also

分布式查询存储的过程(Transact SQL) Distributed Queries Stored Procedures (Transact-SQL)
sp_addlinkedsrvlogin (TRANSACT-SQL) sp_addlinkedsrvlogin (Transact-SQL)
sp_addserver (TRANSACT-SQL) sp_addserver (Transact-SQL)
sp_dropserver (TRANSACT-SQL) sp_dropserver (Transact-SQL)
sp_serveroption (Transact-SQL) sp_serveroption (Transact-SQL)
sp_setnetname (TRANSACT-SQL) sp_setnetname (Transact-SQL)
系统存储过程 (Transact-SQL) System Stored Procedures (Transact-SQL)
系统表 (Transact-SQL)System Tables (Transact-SQL)