sp_addlinkedserver

Creates a linked server, which allows access to distributed, heterogeneous queries against OLE DB data sources. After creating a linked server with sp_addlinkedserver, this server can then execute distributed queries. If the linked server is defined as Microsoft® SQL Server™, remote stored procedures can be executed.

Syntax

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

Arguments

[ @server = ] 'server'

Is the local name of the linked server to create. server is sysname, with no default.

With multiple instances of SQL Server, server may be servername\instancename. The linked server then may be referenced as the data source for

SELECT *FROM    [servername\instancename.]pubs.dbo.authors. 

If data_source is not specified, server is the actual name of the instance.

[ @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 need to be specified.

[ @provider = ] 'provider_name'

Is the unique programmatic identifier (PROGID) of the OLE DB provider corresponding 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. The OLE DB provider is expected to be registered with the given 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), with a default of NULL. data_source is passed as the DBPROP_INIT_DATASOURCE property to initialize the OLE DB provider.

When the linked server is created against the SQL Server OLE DB provider, data_source can be specified in the form of servername\instancename, which can be used to connect to a specific instance of SQL Server running on the specified computer. servername is the name of the computer on which SQL Server is running, and instancename is the name of the specific SQL Server instance to which the user will be connected.

[ @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 passed as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider.

When the linked server is created against the SQL Server OLE DB provider, the instance can be specified 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 SQL Server instance to which the user will be connected.

[ @catalog = ] 'catalog'

Is the catalog to be used when making a connection 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.

Return Code Values

0 (success) or 1 (failure)

Result Sets

sp_addlinkedserver returns this message if no parameters are specified:

Procedure 'sp_addlinkedserver' expects parameter '@server', which was not supplied.

sp_addlinkedserver used with the appropriate OLE DB provider and parameters returns this message:

Server added.

Remarks

The following table shows the ways that a linked server can be set up for data sources accessible through OLE DB. A linked server can be set up using more than one way for a given data source; there may 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 SQL Server. Use server to specify the server.
(2 ) "Any" indicates that the product name can be anything.

The data_source, location, provider_string, and catalog parameters identify the database(s) the linked server points to. If any of these parameters are NULL, the corresponding OLE DB initialization property is not set.

Note  To use the Microsoft OLE DB Provider for SQL Server 2000 in SQL Server version 6.x, run the \Microsoft SQL Server\MSSQL\Install\Instcat.sql script against the version 6.x SQL Server. This script is essential for running distributed queries against an SQL Server 6.x server.

In a clustered environment, when specifying file names to point to OLE DB data sources, use the universal naming convention name (UNC) or a shared drive to specify the location

Permissions

Execute permissions default to members of the sysadmin and setupadmin fixed server roles.

Examples

A. Use the Microsoft OLE DB Provider for SQL Server

1. Creating a linked server using OLE DB for SQL Server

   This example creates a linked server named SEATTLESales that uses the Microsoft OLE DB Provider for SQL Server.

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

2. Creating a linked server on an instance of SQL Server

   This example creates a linked server S1_instance1 on an instance of SQL Server, using the OLE DB Provider for SQL Server.

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

B. Use the Microsoft OLE DB Provider for Jet

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

USE master
GO
-- To use named parameters:
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
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver 
   'SEATTLE Mktg', 
   'OLE DB Provider for Jet',
   'Microsoft.Jet.OLEDB.4.0', 
   'C:\MSOffice\Access\Samples\Northwind.mdb'
GO

C. Use the Microsoft OLE DB Provider for Oracle

This example creates a linked server named LONDON Mktg that uses the Microsoft OLE DB Provider for Oracle and assumes that the SQL*Net alias for the Oracle database is MyServer.

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
   @server = 'LONDON Mktg',
   @srvproduct = 'Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'MyServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver 
   'LONDON Mktg', 
   'Oracle', 
   'MSDAORA',
   'MyServer'
GO

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

This example creates a linked server named SEATTLE Payroll that uses the Microsoft OLE DB Provider for ODBC and the data_source parameter.

Note  The specified ODBC data source name must be defined as System DSN in the server before executing sp_addlinkedserver.

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver 
   @server = 'SEATTLE Payroll', 
   @provider = 'MSDASQL', 
   @datasrc = 'LocalServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver 
   'SEATTLE Payroll', 
   '', 
   'MSDASQL',
   'LocalServer'
GO

E. Use the Microsoft OLE DB Provider for ODBC with the provider_string parameter

This example creates a linked server named LONDON Payroll that uses the Microsoft OLE DB Provider for ODBC and the provider_string parameter.

Note  For more information about ODBC connect strings, see SQLDriverConnect and How to allocate handles and connect to SQL Server (ODBC).

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver 
   @server = 'LONDON Payroll', 
   @provider = 'MSDASQL',
   @provstr = 'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver 
   'LONDON Payroll', 
   '', 
   'MSDASQL',
   NULL,
   NULL,
   'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'
GO

F. Use the Microsoft OLE DB Provider for Jet on an Excel Spreadsheet

To create a linked server definition using the Microsoft OLE DB Provider for Jet to access an Excel spreadsheet, first create a named range in Excel 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

In order to access data from an Excel spreadsheet, associate a range of cells with a name. A given named range can be accessed by using the name of the range as the table name. The following query can be used to access a named range called SalesData using the linked server set up as above.

SELECT *
FROM EXCEL...SalesData
GO

G. Use the Microsoft OLE DB Provider for Indexing Service

This example creates a linked server and uses OPENQUERY to retrieve information from both the linked server and the file system enabled for Indexing Service.

EXEC sp_addlinkedserver FileSystem,
   'Index Server',
   'MSIDXS',
   'Web'
GO
USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'yEmployees')
   DROP TABLE yEmployees
GO
CREATE TABLE yEmployees
 (
  id       int         NOT NULL,
  lname    varchar(30) NOT NULL,
  fname    varchar(30) NOT NULL,
  salary   money,
  hiredate datetime
 )
GO
INSERT yEmployees VALUES
 ( 
  10,
  'Fuller',
  'Andrew',
  $60000,
  '9/12/98'
 )
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'DistribFiles')
   DROP VIEW DistribFiles
GO
CREATE VIEW DistribFiles 
 AS
 SELECT *
 FROM OPENQUERY(FileSystem,
                 'SELECT Directory, 
                    FileName,
                    DocAuthor,
                    Size,
                    Create,
                    Write
                  FROM SCOPE('' "c:\My Documents" '')
                  WHERE CONTAINS(''Distributed'') > 0 
                    AND FileName LIKE ''%.doc%'' ')
 WHERE DATEPART(yy, Write) = 1998
GO
SELECT * 
FROM DistribFiles
GO
SELECT Directory,
  FileName, 
  DocAuthor, 
  hiredate
FROM DistribFiles D, yEmployees E
WHERE D.DocAuthor = E.FName + ' ' + E.LName
GO

H. Use the Microsoft OLE DB Provider for Jet to access a text file

This 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 pathname 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 creating a schema.ini file, refer to Jet Database Engine documentation.

--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 4-part name 
SELECT * 
FROM txtsrv...[file1#txt]

I. Use the Microsoft OLE DB Provider for DB2

This example creates a linked server named DB2 that uses 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;'

See Also

Configuring Linked Servers

OLE DB Providers Tested with SQL Server

sp_addlinkedsrvlogin

sp_addserver

sp_dropserver

sp_serveroption

sp_setnetname

System Stored Procedures

System Tables