sp_addlinkedserver (Transact-SQL)

Artigo
06/18/2013

Cria um servidor vinculado. Um servidor vinculado permite acesso a consultas distribuídas e heterogêneas em fontes de dados OLE DB. Depois que um servidor vinculado é criado usando sp_addlinkedserver, podem ser executadas consultas distribuídas em relação a esse servidor. Se o servidor vinculado estiver definido como uma instância do SQL Server, poderão ser executados procedimentos armazenados remotos.

Ícone de vínculo de tópico Convenções da sintaxe Transact-SQL

Sintaxe

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

Argumentos

[ @server= ] 'server'
É o nome do servidor vinculado a ser criado. server é sysname, sem padrão.
[ @srvproduct= ] 'product_name'
É o nome do produto da fonte de dados OLE DB a ser adicionado como um servidor vinculado. product_name é nvarchar(128), com um padrão de NULL. Se o SQL Server, provider_name, data_source, location, provider_string e catalog não tiverem que ser especificados.
[ @provider= ] 'provider_name'
É o identificador programático exclusivo (PROGID) do provedor OLE DB que corresponde a essa fonte de dados. provider_name deve ser exclusivo para o provedor OLE DB especificado instalado no computador atual. provider_name é nvarchar(128), com um padrão de NULL; porém, se provider_name for omitido, SQLNCLI será usado. (Usar SQLNCLI e SQL Server redirecionará para a versão mais recente do Provedor OLE DB do SQL Server Native Client.) Espera-se que o provedor OLE DB esteja registrado com o PROGID especificado no Registro.
[ @datasrc= ] 'data_source'
É o nome da fonte de dados conforme interpretada pelo provedor OLE DB. data_source é nvarchar(4000). data_source é passado como a propriedade DBPROP_INIT_DATASOURCE para inicializar o provedor OLE DB.
[ @location= ] 'location'
É o local do banco de dados conforme interpretado pelo provedor OLE DB. location é nvarchar(4000), com um padrão de NULL. location é passado como a propriedade DBPROP_INIT_LOCATION para inicializar o provedor OLE DB.

[ @provstr= ] 'provider_string'
É a cadeia de conexão específica ao provedor OLE DB que identifica uma fonte de dados exclusiva. provider_string é nvarchar(4000), com um padrão de NULL. provstr é passado para IDataInitialize ou definido como a propriedade DBPROP_INIT_PROVIDERSTRING para inicializar o provedor OLE DB.

Quando o servidor vinculado é criado em relação ao provedor OLE DB do SQL Server Native Client, a instância pode ser especificada por meio da palavra-chave SERVER como SERVER =servername\instancename para especificar uma instância específica do SQL Server. servername é o nome do computador no qual o SQL Server está em execução, e instancename é o nome da instância específica do SQL Server à qual o usuário será conectado.

Observação
Para acessar um banco de dados espelho, uma cadeia de conexão deve conter o nome do banco de dados. Esse nome é necessário para habilitar tentativas de failover pelo provedor de acesso de dados. O banco de dados pode ser especificado no @provstr ou parâmetro de @catalog. Opcionalmente, a cadeia de conexão também pode fornecer um nome de parceiro de failover.

[ @catalog= ] 'catalog'
É o catálogo a ser usado quando uma conexão for feita ao provedor OLE DB. catalog é sysname, com um padrão de NULL. catalog é passado como a propriedade DBPROP_INIT_CATALOG para inicializar o provedor OLE DB. Quando o servidor vinculado for definido em relação a uma instância do SQL Server, o catálogo se referirá ao banco de dados padrão ao qual o servidor vinculado estará mapeado.

Valores de código de retorno

0 (êxito) ou 1 (falha)

Conjuntos de resultados

Nenhum.

Comentários

A tabela a seguir mostra as formas que um servidor vinculado pode ser definido para que as fontes de dados possam ser acessadas através do OLE DB. Um servidor vinculado pode ser definido em mais de uma forma para uma fonte de dados em particular; pode haver mais de uma linha para um tipo de fonte de dados. Essa tabela também mostra os valores de parâmetro de sp_addlinkedserver a serem usados para configurar o servidor vinculado.

Fonte de dados remota OLE DB.	Provedor OLE DB	product_name	provider_name	data_source	provider_string	catálogo
SQL Server	Provedor OLE DB do Microsoft SQL Server Native Client	SQL Server1 (padrão)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	Nome de rede do SQL Server (para instância padrão)		Nome do banco de dados (opcional)
SQL Server	Provedor OLE DB do Microsoft SQL Server Native Client		SQLNCLI	servername\instancename (para a instância específica)		Nome do banco de dados (opcional)
Oracle, versão 8 e posterior	Provedor Oracle para OLE DB	Qualquer	OraOLEDB.Oracle	Alias para o banco de dados de Oracle
Access/Jet	Microsoft OLE DB Provider for Jet	Qualquer	Microsoft.Jet.OLEDB.4.0	Caminho completo de arquivo de banco de dados de Jet
Fonte de dados ODBC	Microsoft OLE DB Provider para ODBC	Qualquer	MSDASQL	DSN do sistema da fonte de dados ODBC
Fonte de dados ODBC	Microsoft OLE DB Provider para ODBC	Qualquer	MSDASQL		Cadeia de conexão ODBC
Sistema de arquivos	Microsoft OLE DB Provider for Indexing Service	Qualquer	MSIDXS	Nome do catálogo do Indexing Service
Planilha do MicrosoftExcel	MicrosoftOLE DB Provider for Jet	Qualquer	Microsoft.Jet.OLEDB.4.0	Caminho completo do arquivo de Excel	Excel 5.0
Banco de dados IBM DB2	MicrosoftProvedor OLE DB para DB2	Qualquer	DB2OLEDB		Consulte a documentação do Microsoft OLE DB Provider for DB2.	Nome de catálogo do banco de dados DB2

1 Esta forma de definir um servidor vinculado impõe o nome do servidor vinculado para que seja o mesmo nome da rede na instância remota do SQL Server. Use data_source para especificar o servidor.

2 "Qualquer" indica que o nome de produto pode ser qualquer coisa.

O provedor OLE DB Native Client Microsoft SQL Server é o provedor que é usado com o SQL Server se nenhum nome de provedor for especificado ou se o SQL Server for especificado como o nome de produto. Mesmo se você especificar o nome do provedor anterior, SQLOLEDB, será alterado para SQLNCLI quando for persistente para o catálogo.

Os parâmetros data_source, location, provider_string e catalog identificam o banco de dados ou bancos de dados para os quais o servidor vinculado aponta. Se qualquer um destes parâmetros for NULL, a propriedade de inicialização OLE DB correspondente não será definida.

Em um ambiente clusterizado, quando você especificar os nomes de arquivo para apontarem para fontes de dados OLE DB, use o nome UNC (Convenção Universal de nomenclatura) ou um drive compartilhado para especificar o local.

O sp_addlinkedserver não pode ser executado dentro de uma transação definida pelo usuário.

Observação sobre segurança
Quando um servidor vinculado é criado usando sp_addlinkedserver, um automapeamento padrão é adicionado para todos os logons locais. Para provedores não SQL Server, os logos autenticados SQL Server podem conseguir acesso ao provedor sob a conta de serviço SQL Server. Os administradores deveriam considerar o uso de sp_droplinkedsrvlogin <linkedserver_name>, NULL para remover o mapeamento global.

Quando um servidor vinculado é criado usando sp_addlinkedserver, um automapeamento padrão é adicionado para todos os logons locais. Para provedores não SQL Server, os logos autenticados SQL Server podem conseguir acesso ao provedor sob a conta de serviço SQL Server. Os administradores deveriam considerar o uso de sp_droplinkedsrvlogin <linkedserver_name>, NULL para remover o mapeamento global.

Permissões

Requer permissão ALTER ANY LINKED SERVER.

Exemplos

A.Usando o provedor OLE DB do Microsoft SQL Server Native Client

O exemplo seguinte cria um servidor vinculado nomeado SEATTLESales. O nome de produto é SQL Server e nenhum nome de provedor é usado.

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

O exemplo a seguir cria um servidor vinculado S1_instance1 na instância de SQL Server usando o provedor SQL Server Native Client OLE DB.

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

B.Usando o Provedor Microsoft OLE DB para Microsoft Access

O provedor Microsoft.Jet.OLEDB.4.0 se conecta a bancos de dados Microsoft Access que usam o formato 2002-2003. O exemplo abaixo cria um servidor vinculado chamado SEATTLE Mktg.

Observação
Esse exemplo presume que o Microsoft Access e o banco de dados de exemplo Northwind estão instalados e que o banco de dados Northwind reside em 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

O provedor Microsoft.ACE.OLEDB.12.0 se conecta a bancos de dados Microsoft Access que usam o formato 2007. O exemplo abaixo cria um servidor vinculado chamado SEATTLE Mktg.

Observação
Esse exemplo presume que o Microsoft Access e o banco de dados de exemplo Northwind estão instalados e que o banco de dados Northwind reside em 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.Usando o Microsoft OLE DB Provider para ODBC com o parâmetro data_source

O exemplo a seguir cria um servidor vinculado nomeado SEATTLE Payroll que usa o Microsoft OLE DB Provider for ODBC (MSDASQL) e o parâmetro data_source.

Observação
O nome da fonte de dados ODBC especificado deve ser definido como DSN do sistema antes de você usar o servidor vinculado.

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

D.Usando o Provedor Microsoft OLE DB para planilha do Excel

Para criar uma definição de servidor vinculado usando o Microsoft OLE DB Provider for Jet para acessar uma planilha do Excel no formato 1997 - 2003, crie primeiro um intervalo nomeado no Excel especificando as colunas e linhas da planilha do Excel que deverão ser selecionadas. O nome do intervalo pode ser então referenciado como um nome de tabela em uma consulta distribuída.

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

Para acessar dados de uma planilha do Excel, associe um intervalo de células com um nome. A consulta a seguir pode ser usada para acessar um intervalo nomeado especificado SalesData como uma tabela usando a configuração do servidor vinculado anterior.

SELECT *
   FROM ExcelSource...SalesData;
GO

Se o SQL Server estiver sendo executado sob uma conta de domínio que tenha acesso a um compartilhamento remoto, um caminho UNC poderá ser usado ao invés de um drive mapeado.

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

Para se conectar a uma planilha do Excel no formato Excel 2007, use o provedor 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.Usando o Microsoft OLE DB Provider para Jet para acessar um arquivo de texto

O exemplo a seguir cria um servidor vinculado para acessar arquivos de texto diretamente sem vincular os arquivos como tabelas em um arquivo .mdb do Access. O provedor é Microsoft.Jet.OLEDB.4.0 e a cadeia de caracteres do provedor é Text.

A fonte de dados é o caminho completo do diretório que contém os arquivos de texto. Um arquivo schema.ini, que descreve a estrutura dos arquivos de texto, deve existir no mesmo diretório que os arquivos de texto. Para obter mais informações sobre como criar um arquivo Schema.ini, consulte a documentação do Mecanismo de Banco de Dados do 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.Usando o Microsoft OLE DB Provider para DB2

O exemplo seguinte cria um servidor vinculado nomeado DB2 que usa a 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;';