sp_addlinkedserver (Transact-SQL)

Artículo
06/18/2013

Crea un servidor vinculado. Un servidor vinculado permite obtener acceso a consultas heterogéneas distribuidas en orígenes de datos OLE DB. Después de crear un servidor vinculado mediante sp_addlinkedserver, se pueden ejecutar consultas distribuidas en este servidor. Si el servidor vinculado se define como una instancia de SQL Server, se pueden ejecutar procedimientos almacenados remotos.

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Argumentos

[ @server= ] 'server'
Es el nombre del servidor vinculado que se va a crear. server es de tipo sysname y no tiene ningún valor predeterminado.
[ @srvproduct= ] 'product_name'
Es el nombre del producto del origen de datos OLE DB para agregarlo como servidor vinculado. product_name es de tipo nvarchar(128) y su valor predeterminado es NULL. En SQL Server no necesita especificar provider_name, data_source, location, provider_string ni catalog.
[ @provider= ] 'provider_name'
Es el identificador de programación (PROGID) único del proveedor OLE DB que corresponde a este origen de datos. provider_name debe ser único para el proveedor OLE DB especificado que está instalado en el equipo actual. provider_name es de tipo nvarchar(128) y su valor predeterminado es NULL; sin embargo, si se omite provider_name, se utiliza SQLNCLI. (Si se usa SQLNCLI, SQL Server redirigirá a la última versión del proveedor OLE DB del proveedor OLE DB de SQL Server Native Client). Se espera que el proveedor OLE DB se registre con el PROGID especificado en el Registro.
[ @datasrc= ] 'data_source'
Es el nombre del origen de datos como lo interpreta el proveedor OLE DB. data_source es de tipo nvarchar(4000). data_source se pasa como propiedad DBPROP_INIT_DATASOURCE para inicializar el proveedor OLE DB.
[ @location= ] 'location'
Es la ubicación de la base de datos según la interpretación del proveedor OLE DB. location es de tipo nvarchar(4000) y su valor predeterminado es NULL. location se pasa como la propiedad DBPROP_INIT_LOCATION para inicializar el proveedor OLE DB.
[ @provstr= ] 'provider_string'
Es la cadena de conexión específica del proveedor OLE DB que identifica un origen de datos único. provider_string es de tipo nvarchar(4000) y su valor predeterminado es NULL. provstr se pasa a IDataInitialize o bien se establece como la propiedad DBPROP_INIT_PROVIDERSTRING para inicializar el proveedor OLE DB.

Cuando se crea el servidor vinculado para el proveedor OLE DB de SQL Server Native Client, la instancia puede especificarse mediante la palabra clave SERVER en SERVER=servername\instancename a fin de especificar una instancia concreta de SQL Server. servername es el nombre del equipo en el que SQL Server se está ejecutando, y instancename es el nombre de la instancia concreta de SQL Server con la que el usuario se conecta.

[!NOTA]

Para tener acceso a una base de datos reflejada, una cadena de conexión debe contener el nombre de la base de datos. Este nombre es necesario para que el proveedor de acceso a datos pueda intentar la conmutación por error. La base de datos se puede especificar en el parámetro @provstr o @catalog. Opcionalmente, la cadena de conexión también puede proporcionar un nombre de asociado de conmutación por error.
[ @catalog= ] 'catalog'
Es el catálogo que debe utilizarse al establecer una conexión con el proveedor OLE DB. catalog es de tipo sysname y su valor predeterminado es NULL. catalog se pasa como la propiedad DBPROP_INIT_CATALOG para inicializar el proveedor OLE DB. Cuando se define el servidor vinculado en una instancia de SQL Server, catalog se refiere a la base de datos predeterminada a la que se asigna el servidor vinculado.

Valores de código de retorno

0 (correcto) o 1 (error)

Conjuntos de resultados

Ninguno.

Comentarios

En la tabla siguiente se muestran las distintas maneras de configurar un servidor vinculado para los orígenes de datos a los que se puede tener acceso a través de OLE DB. Existen varias formas de configurar un servidor vinculado para un origen de datos determinado; puede haber más de una fila por tipo de origen de datos. En la tabla siguiente también se muestran los valores de parámetro de sp_addlinkedserver que se deben utilizar para configurar el servidor vinculado.

Origen de datos remotos de OLE DB	Proveedor OLE DB	product_name	provider_name	data_source	provider_string	catalog
SQL Server	Proveedor OLE DB de Microsoft SQL Server Native Client	SQL Server1 (predeterminado)
SQL Server	Proveedor OLE DB de Microsoft SQL Server Native Client		SQLNCLI	Nombre de red de SQL Server (para la instancia predeterminada)		Nombre de la base de datos (opcional)
SQL Server	Proveedor OLE DB de Microsoft SQL Server Native Client		SQLNCLI	servername\instancename (para la instancia específica)		Nombre de la base de datos (opcional)
Oracle, versión 8 y posterior	Proveedor Oracle para OLE DB	Cualquiera	OraOLEDB.Oracle	Alias para base de datos Oracle
Access/Jet	Proveedor Microsoft OLE DB para Jet	Cualquiera	Microsoft.Jet.OLEDB.4.0	Ruta de acceso completa del archivo de base de datos Jet
Origen de datos ODBC	Proveedor Microsoft OLE DB para ODBC	Cualquiera	MSDASQL	DSN del sistema del origen de datos ODBC
Origen de datos ODBC	Proveedor Microsoft OLE DB para ODBC	Cualquiera	MSDASQL		Cadena de conexión ODBC
Sistema de archivos	Proveedor Microsoft OLE DB para Servicios de Index Server	Cualquiera	MSIDXS	Nombre de catálogo de Servicios de Index Server
Hojas de cálculo de Microsoft Excel	Proveedor Microsoft OLE DB para Jet	Cualquiera	Microsoft.Jet.OLEDB.4.0	Ruta de acceso completa del archivo Excel	Excel 5.0
Bases de datos IBM DB2	Proveedor Microsoft OLE DB para DB2	Cualquiera	DB2OLEDB		Vea la documentación del proveedor Microsoft OLE DB para DB2.	Nombre del catálogo de la base de datos DB2

1 Esta forma de configurar un servidor vinculado obliga a que el nombre del servidor vinculado sea igual al nombre de red de la instancia remota de SQL Server. Utilice data_source para especificar el servidor.

2 "Cualquiera" indica que el nombre de producto puede ser cualquier nombre.

El proveedor OLE DB de Microsoft SQL Server Native Client (SQLNCLI) es el proveedor utilizado con SQL Server si no se especifica un nombre de proveedor o si se especifica SQL Server como nombre de producto. Incluso si especifica el nombre de proveedor antiguo, SQLOLEDB, éste se cambiará a SQLNCLI cuando se mantenga en el catálogo.

Los parámetros data_source, location, provider_string y catalog identifican la base o bases de datos a las que apunta en servidor vinculado. Si alguno de estos parámetros es NULL, no se establecerá la propiedad de inicialización de OLE DB correspondiente.

En un entorno en clúster, al especificar nombres de archivo para apuntar a orígenes de datos OLE DB, utilice el nombre UNC (convención de nomenclatura universal) o una unidad compartida para especificar la ubicación.

sp_addlinkedserver no se puede ejecutar dentro de una transacción definida por el usuario.

Nota de seguridad
Cuando se agrega un servidor vinculado utilizando sp_addlinkedserver, se agrega una autoasignación predeterminada para todos los inicios de sesión locales. En el caso de proveedores que no son de SQL Server, los inicios de sesión autenticados de SQL Server pueden obtener acceso al proveedor con la cuenta de servicio de SQL Server. Los administradores deben tener en cuenta el uso de sp_droplinkedsrvlogin <linkedserver_name>, NULL para quitar la asignación global.

Cuando se agrega un servidor vinculado utilizando sp_addlinkedserver, se agrega una autoasignación predeterminada para todos los inicios de sesión locales. En el caso de proveedores que no son de SQL Server, los inicios de sesión autenticados de SQL Server pueden obtener acceso al proveedor con la cuenta de servicio de SQL Server. Los administradores deben tener en cuenta el uso de sp_droplinkedsrvlogin <linkedserver_name>, NULL para quitar la asignación global.

Permisos

Requiere el permiso ALTER ANY LINKED SERVER.

Ejemplos

A.Usar el proveedor OLE DB de Microsoft SQL Server Native Client

En el siguiente ejemplo se crea un servidor vinculado llamado SEATTLESales. El nombre del producto es SQL Server y no se utiliza ningún nombre de proveedor.

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

En el siguiente ejemplo se crea un servidor vinculado denominado S1_instance1 en una instancia de SQL Server mediante el uso del proveedor OLE DB de SQL Server Native Client.

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

B.Usar el proveedor Microsoft OLE DB para Microsoft Access

El proveedor de Microsoft.Jet.OLEDB.4.0 conecta a las bases de datos de Microsoft Access que usan el formato 2002-2003. En el siguiente ejemplo se crea un servidor vinculado denominado SEATTLE Mktg.

[!NOTA]

En este ejemplo se da por supuesto que se han instalado Microsoft Access y la base de datos de ejemplo Northwind, y que la base de datos Northwind reside en 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

El proveedor de Microsoft.ACE.OLEDB.12.0 conecta a las bases de datos de Microsoft Access que usan el formato 2007. En el siguiente ejemplo se crea un servidor vinculado denominado SEATTLE Mktg.

[!NOTA]

En este ejemplo se da por supuesto que se han instalado Microsoft Access y la base de datos de ejemplo Northwind, y que la base de datos Northwind reside en 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.Usar el proveedor Microsoft OLE DB para ODBC con el parámetro data_source

En el siguiente ejemplo se crea un servidor vinculado denominado SEATTLE Payroll que utiliza el proveedor Microsoft OLE DB para ODBC (MSDASQL) y el parámetro data_source.

[!NOTA]

El nombre del origen de datos ODBC especificado debe definirse como DSN del sistema en el servidor antes de utilizar el servidor vinculado.

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

D.Usar el proveedor Microsoft OLE DB para una hoja de cálculo de Excel

Para crear una definición de servidor vinculado mediante el proveedor Microsoft OLE DB para Jet para obtener acceso a una hoja de cálculo de Excel en formato 1997 - 2003, primero debe crearse un intervalo con nombre en Excel que especifique las columnas y filas de la hoja de cálculo de Excel que han de seleccionarse. Entonces, podrá hacerse referencia al nombre del intervalo como un nombre de tabla en una consulta distribuida.

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

Para obtener acceso a los datos de una hoja de cálculo Excel, asocie un nombre a un intervalo de celdas. La consulta siguiente se puede utilizar para obtener acceso al intervalo denominado SalesData especificado como una tabla utilizando el servidor vinculado que se creó anteriormente.

SELECT *
   FROM ExcelSource...SalesData;
GO

Si SQL Server se ejecuta en una cuenta de dominio que tiene acceso a un recurso compartido remoto, se puede utilizar una ruta de acceso UNC en lugar de una unidad asignada.

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

Para conectarse a una hoja de cálculo de Excel en formato de Excel 2007, use el proveedor 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.Usar el proveedor Microsoft OLE DB para Jet para tener acceso a un archivo de texto

En este ejemplo se crea un servidor vinculado para obtener acceso directamente a archivos de texto, sin necesidad de vincular los archivos como tablas en un archivo .mdb de Access. El proveedor es Microsoft.Jet.OLEDB.4.0 y la cadena de proveedor es Text.

El origen de datos es la ruta de acceso completa del directorio que contiene los archivos de texto. En el mismo directorio que los archivos de texto, debe existir un archivo schema.ini, que describe la estructura de dichos archivos. Para obtener más información sobre cómo crear un archivo schema.ini, vea la documentación relativa al motor de bases de datos 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.Usar el proveedor Microsoft OLE DB para DB2

En el siguiente ejemplo se crea un servidor vinculado llamado DB2 que utiliza el 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;';