[ @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 SQL Server will redirect to the latest version of SQL 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'

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 SQL 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 Server. servername is the name of the computer on which SQL Server is running, and instancename is the name of the specific instance of SQL Server 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. For more information, see Making the Initial Connection to a Database Mirroring Session.

[ @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 SQL Server, 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.

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 SQL Server. Use data_source to specify the server.

2 "Any" indicates that the product name can be anything.

The Microsoft SQL Server Native Client OLE DB provider is the provider that is used with SQL Server if no provider name is specified or if SQL Server 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.

Security Note:
When a linked server is created by using sp_addlinkedserver, a default self-mapping is added for all local logins. For non-SQL Server providers, SQL Server Authenticated logins may be able to gain access to the provider under the SQL Server service account. Administrators should consider using sp_droplinkedsrvlogin <linkedserver_name>, NULL to remove the global mapping.

Requires ALTER ANY LINKED SERVER permission.

A. Using the Microsoft SQL Server Native Client OLE DB Provider

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

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

B. Using the Microsoft OLE DB Provider for Microsoft Access

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 Microsoft Access and the sample Northwind database are installed and that the Northwind database resides in C:\Msoffice\Access\Samples.

EXEC sp_addlinkedserver 
   @server = 'SEATTLE Mktg', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = '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 Microsoft Access and the sample Northwind database are installed and that the Northwind database resides in C:\Msoffice\Access\Samples.

EXEC sp_addlinkedserver 
   @server = 'SEATTLE Mktg', 
   @provider = Microsoft.ACE.OLEDB.12.0', 
   @srvproduct = 'OLE DB Provider for ACE',
   @datasrc = 'C:\MSOffice\Access\Samples\Northwind.accdb'
GO

Using the Microsoft OLE DB Provider for Oracle

EXEC sp_addlinkedserver
   @server = 'LONDON Mktg',
   @srvproduct = 'Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'MyServer'
GO

D. Using the Microsoft OLE DB Provider for ODBC with the data_source parameter

The following example creates a linked server named SEATTLE Payroll that uses the Microsoft 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 = 'SEATTLE Payroll', 
   @srvproduct = '',
   @provider = 'MSDASQL', 
   @datasrc = 'LocalServer'
GO

E. Using the Microsoft OLE DB Provider for Excel spreadsheet

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

SELECT *
   FROM ExcelSource...SalesData
GO

EXEC sp_addlinkedserver 'ExcelShare',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   '\\MyServer\MyShare\Spreadsheets\DistExcl.xls',
   NULL,
   'Excel 5.0'

EXEC sp_addlinkedserver @server = N'ExcelDataSource', 
@srvproduct=N'ExcelData', @provider=N'Microsoft.ACE.OLEDB.12.0', 
@datasrc=N'C:\DataFolder\People.xlsx',
@provstr='EXCEL 12.0' ;

F. Using the Microsoft OLE DB Provider for Jet to access a text file

--Create a linked server.
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 
   'Microsoft.Jet.OLEDB.4.0',
   'c:\data\distqry',
   NULL,
   '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]

G. Using the Microsoft OLE DB Provider for DB2

EXEC sp_addlinkedserver
   @server='DB2',
   @srvproduct='Microsoft OLE DB Provider for DB2',
   @catalog='DB2',
   @provider='DB2OLEDB',
   @provstr='Initial Catalog=PUBS;
       Data Source=DB2;
       HostCCSID=1252;
       Network Address=XYZ;
       Network Port=50000;
       Package Collection=admin;
       Default Schema=admin;'

Reference

Other Resources

Help and Information