| title | sp_addlinkedserver (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 09/12/2016 | ||
| ms.prod | sql-non-specified | ||
| ms.prod_service | database-engine | ||
| ms.service | |||
| ms.component | system-stored-procedures | ||
| ms.reviewer | |||
| ms.suite | sql | ||
| ms.technology |
|
||
| ms.tgt_pltfrm | |||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | fed3adb0-4c15-4a1a-8acd-1b184aff558f | ||
| caps.latest.revision | 70 | ||
| author | edmacauley | ||
| ms.author | edmaca | ||
| manager | craigg | ||
| ms.workload | Active |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]
Creates a linked server. A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created by using sp_addlinkedserver, distributed queries can be run against this server. If the linked server is defined as an instance of [!INCLUDEssNoVersion], remote stored procedures can be executed.
Transact-SQL Syntax Conventions
sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
[ , [ @provider= ] 'provider_name' ]
[ , [ @datasrc= ] 'data_source' ]
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string' ]
[ , [ @catalog= ] 'catalog' ]
[ @server= ] 'server'
Is the name of the linked server to create. server is sysname, with no default.
[ @srvproduct= ] 'product_name'
Is the product name of the OLE DB data source to add as a linked server. product_name is nvarchar(128), with a default of NULL. If SQL Server, provider_name, data_source, location, provider_string, and catalog do not have to be specified.
[ @provider= ] 'provider_name'
Is the unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to this data source. provider_name must be unique for the specified OLE DB provider installed on the current computer. provider_name is nvarchar(128), with a default of NULL; however, if provider_name is omitted, SQLNCLI is used. (Use SQLNCLI and [!INCLUDEssNoVersion] will redirect to the latest version of [!INCLUDEssNoVersion] Native Client OLE DB Provider.) The OLE DB provider is expected to be registered with the specified PROGID in the registry.
[ @datasrc= ] 'data_source'
Is the name of the data source as interpreted by the OLE DB provider. data_source is nvarchar(4000). data_source is passed as the DBPROP_INIT_DATASOURCE property to initialize the OLE DB provider.
[ @location= ] 'location'
Is the location of the database as interpreted by the OLE DB provider. location is nvarchar(4000), with a default of NULL. location is passed as the DBPROP_INIT_LOCATION property to initialize the OLE DB provider.
[ @provstr= ] 'provider_string'
Is the OLE DB provider-specific connection string that identifies a unique data source. provider_string is nvarchar(4000), with a default of NULL. provstr is either passed to IDataInitialize or set as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider.
When the linked server is created against the [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion]. servername is the name of the computer on which [!INCLUDEssNoVersion] is running, and instancename is the name of the specific instance of [!INCLUDEssNoVersion] to which the user will be connected.
Note
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. The database can be specified in the @provstr or @catalog parameter. Optionally, the connection string can also supply a failover partner name.
[ @catalog= ] 'catalog'
Is the catalog to be used when a connection is made to the OLE DB provider. catalog is sysname, with a default of NULL. catalog is passed as the DBPROP_INIT_CATALOG property to initialize the OLE DB provider. When the linked server is defined against an instance of [!INCLUDEssNoVersion], catalog refers to the default database to which the linked server is mapped.
0 (success) or 1 (failure)
None.
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. This table also shows the sp_addlinkedserver parameter values to be used for setting up the linked server.
| Remote OLE DB data source | OLE DB provider | product_name | provider_name | data_source | location | provider_string | catalog |
|---|---|---|---|---|---|---|---|
| [!INCLUDEssNoVersion] | [!INCLUDEmsCoName] [!INCLUDEssNoVersion] Native Client OLE DB Provider | [!INCLUDEssNoVersion] 1 (default) | |||||
| [!INCLUDEssNoVersion] | [!INCLUDEmsCoName] [!INCLUDEssNoVersion] Native Client OLE DB Provider | SQLNCLI | Network name of [!INCLUDEssNoVersion] (for default instance) | Database name (optional) | |||
| [!INCLUDEssNoVersion] | [!INCLUDEmsCoName] [!INCLUDEssNoVersion] Native Client OLE DB Provider | SQLNCLI | servername\instancename (for specific instance) | Database name (optional) | |||
| Oracle, version 8 and later | Oracle Provider for OLE DB | Any | OraOLEDB.Oracle | Alias for the Oracle database | |||
| Access/Jet | Microsoft OLE DB Provider for Jet | Any | Microsoft.Jet.OLEDB.4.0 | Full path of Jet database file | |||
| ODBC data source | Microsoft OLE DB Provider for ODBC | Any | MSDASQL | System DSN of ODBC data source | |||
| ODBC data source | [!INCLUDEmsCoName] OLE DB Provider for ODBC | Any | MSDASQL | ODBC connection string | |||
| File system | [!INCLUDEmsCoName] OLE DB Provider for Indexing Service | Any | MSIDXS | Indexing Service catalog name | |||
| [!INCLUDEmsCoName] Excel Spreadsheet | [!INCLUDEmsCoName] OLE DB Provider for Jet | Any | Microsoft.Jet.OLEDB.4.0 | Full path of Excel file | Excel 5.0 | ||
| IBM DB2 Database | [!INCLUDEmsCoName] OLE DB Provider for DB2 | Any | DB2OLEDB | See [!INCLUDEmsCoName] OLE DB Provider for DB2 documentation. | Catalog name of DB2 database |
1 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 [!INCLUDEssNoVersion]. Use data_source to specify the server.
2 "Any" indicates that the product name can be anything.
The [!INCLUDEmsCoName] [!INCLUDEssNoVersion] Native Client OLE DB provider is the provider that is used with [!INCLUDEssNoVersion] if no provider name is specified or if [!INCLUDEssNoVersion] is specified as the product name. Even if you specify the older provider name, SQLOLEDB, it will be changed to SQLNCLI when persisted to the catalog.
The data_source, location, provider_string, and catalog parameters identify the database or databases the linked server points to. If any one of these parameters is NULL, the corresponding OLE DB initialization property is not set.
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 cannot be executed within a user-defined transaction.
Important
When a linked server is created by using sp_addlinkedserver, a default self-mapping is added for all local logins. For non-[!INCLUDEssNoVersion] providers, [!INCLUDEssNoVersion] Authenticated logins may be able to gain access to the provider under the [!INCLUDEssNoVersion] service account. Administrators should consider using sp_droplinkedsrvlogin <linkedserver_name>, NULL to remove the global mapping.
The sp_addlinkedserver statement requires the ALTER ANY LINKED SERVER permission. (The SSMS New Linked Server dialog box is implemented in a way that requires membership in the sysadmin fixed server role.)
The following example creates a linked server named SEATTLESales. The product name is SQL Server, and no provider name is used.
USE master;
GO
EXEC sp_addlinkedserver
N'SEATTLESales',
N'SQL Server';
GO
The following example creates a linked server S1_instance1 on an instance of [!INCLUDEssNoVersion] by using the [!INCLUDEssNoVersion] Native Client OLE DB provider.
EXEC sp_addlinkedserver
@server=N'S1_instance1',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'S1\instance1';
The Microsoft.Jet.OLEDB.4.0 provider connects to Microsoft Access databases that use the 2002-2003 format. The following example creates a linked server named SEATTLE Mktg.
Note
This example assumes that both [!INCLUDEmsCoName] 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
The Microsoft.ACE.OLEDB.12.0 provider connects to Microsoft Access databases that use the 2007 format. The following example creates a linked server named SEATTLE Mktg.
Note
This example assumes that both [!INCLUDEmsCoName] 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
The following example creates a linked server named SEATTLE Payroll that uses the [!INCLUDEmsCoName] OLE DB Provider for ODBC (MSDASQL) and the data_source parameter.
Note
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
To create a linked server definition using the [!INCLUDEmsCoName] 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
To access data from an Excel spreadsheet, associate a range of cells with a name. 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
If [!INCLUDEssNoVersion] 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';
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' ;
The following example creates a linked server for directly accessing text files, without linking the files as tables in an Access .mdb file. The 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. A schema.ini file, which describes the structure of the text files, must exist in the same directory as the text files. 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];
The 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. Add a [!INCLUDEssSDSfull] as a Linked Server For Use With Distributed Queries on Cloud and On-Premise Databases
You can add a [!INCLUDEssSDSfull] as a linked server and then use it with distributed queries that span the on-premises and cloud databases. This is a component for database hybrid solutions spanning on-premises corporate networks and the Windows Azure cloud.
The [!INCLUDEssNoVersion] 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-[!INCLUDEssNoVersion] data sources) defined as linked servers. Every [!INCLUDEssSDSfull] (except the virtual master) can be added as an individual linked server and then used directly in your database applications as any other database.
The benefits of using [!INCLUDEssSDSfull] include manageability, high availability, scalability, working with a familiar development model, and a relational data model. The requirements of your database application determine how it would use [!INCLUDEssSDSfull] in the cloud. You can move all of your data at once to [!INCLUDEssSDSfull], or progressively move some of your data while keeping the remaining data on-premises. For such a hybrid database application, [!INCLUDEssSDSfull] can now be added as linked servers and the database application can issue distributed queries to combine data from [!INCLUDEssSDSfull] and on-premise data sources.
Here’s a simple example explaining how to connect to a [!INCLUDEssSDSfull] 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
Distributed Queries Stored Procedures (Transact-SQL)
sp_addlinkedsrvlogin (Transact-SQL)
sp_addserver (Transact-SQL)
sp_dropserver (Transact-SQL)
sp_serveroption (Transact-SQL)
sp_setnetname (Transact-SQL)
System Stored Procedures (Transact-SQL)
System Tables (Transact-SQL)