sp_addlinkedserver (Transact-SQL)

Aplica-se a: SQL Server (todas as versões com suporte) Instância Gerenciada de SQL do Azure

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, as consultas distribuídas podem ser executadas nesse servidor. Se o servidor vinculado for definido como uma instância do SQL Server, os procedimentos armazenados remotos poderão ser executados.

Topic link iconConvenções de sintaxe do 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. O servidor de argumentos é sysname, sem padrão.

[ @srvproduct = ] 'product_name'

É o nome do produto da fonte de dados OLE DB a ser adicionado como um servidor vinculado. O valor product_name é nvarchar(128), com um padrão de NULL. Se o valor for SQL Server, provider_name, data_source, local, provider_string e catálogo não precisarão ser especificados.

[ @provider = ] 'provider_name'

É o identificador programático exclusivo (PROGID) do provedor OLE DB que corresponde a essa fonte de dados. O provider_name deve ser exclusivo para o provedor OLE DB especificado instalado no computador atual. O valor provider_name é nvarchar(128).

  • Antes de SQL Server 2022 (16.x), se @provider for omitido, SQLNCLI será usado. Usar SQLNCLI redirecionará o SQL Server para a última versão do provedor OLE DB do SQL Server Native Client. Espera-se que o provedor OLE DB seja registrado com o PROGID especificado fornecido no Registro. Em vez de SQLNCLI, MSOLEDBSQL é recomendado.
  • A partir do SQL Server 2022 (16.x), você deve especificar um nome de provedor. O MSOLEDBSQL é recomendado.

Importante

O SQL Server Native Client (geralmente abreviado SNAC) foi removido do SQL Server 2022 (16.x) e do SSMS (SQL Server Management Studio 19). Tanto o provedor OLE DB SQL Server Native Client (SQLNCLI ou SQLNCLI11) quanto o Provedor OLE DB herdado da Microsoft para SQL Server (SQLOLEDB) não são recomendados para desenvolvimento novo. Alterne para o novo Driver do Microsoft OLE DB (MSOLEDBSQL) para SQL Server no futuro.

[ @datasrc = ] 'data_source'

É o nome da fonte de dados conforme interpretada pelo provedor OLE DB. O valor data_source é nvarchar(4000). data_source é passada 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. O local do valor é nvarchar(4000), com um padrão de NULL. O local do argumento é 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. O valor provider_string é nvarchar(4000), com um padrão de NULL. O argumento provstr é passado para IDataInitialize ou definido como a propriedade DBPROP_INIT_PROVIDERSTRING para inicializar o provedor OLE DB.

Quando o servidor vinculado é criado no provedor OLE DB SQL Server Native Client, a instância pode ser especificada usando a palavra-chave SERVER como SERVER=servername\\instancename para especificar uma instância específica do SQL Server. O nome do servidor é o nome do computador no qual SQL Server está em execução e instancename é o nome da instância específica do SQL Server ao 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 parâmetro @provstr ou @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. O catálogo de valores é sysname, com um padrão de NULL. O catálogo de argumentos é passado como a propriedade DBPROP_INIT_CATALOG para inicializar o provedor OLE DB. Quando o servidor vinculado é definido em relação a uma instância do SQL Server, catalog se refere ao banco de dados padrão ao qual o servidor vinculado é mapeado.

Valores do 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. Esta tabela também mostra os valores de sp_addlinkedserver parâmetro a serem usados para configurar o servidor vinculado.

Fonte de dados remota OLE DB. Provedor OLE DB product_name provider_name Data_source local Provider_string catalog
SQL Server Provedor OLE DB do Microsoft SQL Server Native Client SQL Server 1 (padrão)
SQL Server Provedor OLE DB do Microsoft SQL Server Native Client SQLNCLI Nome de rede de 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 uma 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 Microsoft Excel Microsoft OLE DB Provider for Jet Qualquer Microsoft.Jet.OLEDB.4.0 Caminho completo do arquivo de Excel Excel 5.0
Banco de dados IBM DB2 Microsoft OLE DB Provider for DB2 Qualquer DB2OLEDB Consulte Provedor Microsoft OLE DB para DB2 documentação. Nome de catálogo do banco de dados DB2

1 Essa maneira de configurar um servidor vinculado força o nome do servidor vinculado a ser o mesmo que o nome de rede da instância remota do SQL Server. Use data_source para especificar o servidor.

2 "Any" indica que o nome do produto pode ser qualquer coisa.

O provedor OLE DB do Microsoft SQL Server Native Client é o provedor usado com SQL Server se nenhum nome de provedor for especificado ou se SQL Server for especificado como o nome do 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, local, provider_string e catálogo identificam o banco de dados ou os 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 procedimento sp_addlinkedserver armazenado não pode ser executado em uma transação definida pelo usuário.

Importante

Instância Gerenciada de SQL do Azure atualmente dá suporte apenas a SQL Server, Banco de Dados SQL e outros Instância Gerenciada de SQL como fontes de dados remotas.

Importante

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

Permissões

A sp_addlinkedserver instrução requer a permissão ALTER ANY LINKED SERVER . (A caixa de diálogo SQL Server Management Studio Novo Servidor Vinculado é implementada de forma a exigir associação na sysadmin função de servidor fixa.)

Exemplos

a. Usar o Provedor OLE DB do Microsoft SQL Server

O exemplo abaixo cria um servidor vinculado chamado 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 S1_instance1 vinculado em uma instância do SQL Server usando o driver OLE DB do SQL Server.

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

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

Importante

O SQL Server Native Client OLE DB (SQLNCLI) permanece preterido e não é recomendável usá-lo para um novo trabalho de desenvolvimento. Em vez disso, use o novo Driver do Microsoft OLE DB para SQL Server (MSOLEDBSQL), que será atualizado com os recursos de servidor mais recentes.

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

B. Usar o Provedor OLE DB da Microsoft 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

Este exemplo pressupõe que o Microsoft Access e o Northwind banco de dados de exemplo estão instalados e que o Northwind banco de dados reside em C:\Msoffice\Access\Samples no mesmo servidor que a instância do SQL Server.

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  

C. Usar o Provedor OLE DB da Microsoft para ODBC com o parâmetro data_source

O exemplo a seguir cria um servidor vinculado chamado SEATTLE Payroll que usa o Provedor OLE DB da Microsoft para 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. Usar a planilha Provedor OLE DB da Microsoft para 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, primeiro crie um intervalo nomeado no Excel especificando as colunas e linhas da planilha do Excel a serem 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 SQL Server estiver em execução em uma conta de domínio que tenha acesso a um compartilhamento remoto, um caminho UNC poderá ser usado em vez de uma unidade mapeada.

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

E. Usar o Provedor OLE DB da Microsoft 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 de schema.ini, consulte a documentação do Mecanismo de Banco de Dados Jet.

Primeiro, crie um servidor vinculado.

EXEC sp_addlinkedserver txtsrv, N'Jet 4.0',   
   N'Microsoft.Jet.OLEDB.4.0',  
   N'c:\data\distqry',  
   NULL,  
   N'Text';  

Configurar mapeamentos de logon.

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL;  

Liste as tabelas no servidor vinculado.

EXEC sp_tables_ex txtsrv;  

Consulte uma das tabelas, nesse caso file1#txt, usando um nome de quatro partes.

SELECT * FROM txtsrv...[file1#txt];  

F. Usar o Provedor Microsoft OLE DB 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;';  

G. Adicionar um banco de dados SQL do Azure como um servidor vinculado para uso com consultas distribuídas em bancos de dados locais e na nuvem

Você pode adicionar um banco de dados SQL do Azure como um servidor vinculado e usá-lo com consultas distribuídas que abrangem os bancos de dados locais e na nuvem. Esse é um componente para soluções híbridas de banco de dados que abrangem redes corporativas locais e a nuvem do Azure.

O produto da caixa de SQL Server contém o recurso de consulta distribuída, que permite que você escreva consultas para combinar dados de fontes de dados locais e dados de fontes remotas (incluindo dados de fontes de dados não SQL Server) definidas como servidores vinculados. Cada banco de dados SQL do Azure (exceto o banco de dados do master servidor lógico) pode ser adicionado como um servidor vinculado individual e, em seguida, usado diretamente em seus aplicativos de banco de dados como qualquer outro banco de dados.

Os benefícios de usar SQL do Azure Banco de Dados incluem capacidade de gerenciamento, alta disponibilidade, escalabilidade, trabalho com um modelo de desenvolvimento familiar e um modelo de dados relacionais. Os requisitos do aplicativo de banco de dados determinam como ele usaria SQL do Azure Banco de Dados na nuvem. Você pode mover todos os seus dados de uma vez para SQL do Azure Banco de Dados ou mover progressivamente alguns de seus dados enquanto mantém os dados restantes no local. Para esse aplicativo de banco de dados híbrido, SQL do Azure Banco de Dados agora pode ser adicionado como servidores vinculados e o aplicativo de banco de dados pode emitir consultas distribuídas para combinar dados de SQL do Azure Banco de Dados e fontes de dados locais.

Aqui está um exemplo simples explicando como se conectar a um banco de dados SQL do Azure usando consultas distribuídas.

Primeiro, adicione um banco de dados SQL do Azure como servidor vinculado, usando o SQL Server Native Client using.

EXEC sp_addlinkedserver  
  @server='LinkedServerName', 
  @srvproduct='',       
  @provider='sqlncli', 
  @datasrc='ServerName.database.windows.net',   
  @location='',  
  @provstr='',  
  @catalog='DatabaseName'; 

Adicione credenciais e opções a este servidor vinculado.

EXEC sp_addlinkedsrvlogin  
  @rmtsrvname = 'LinkedServerName',  
  @useself = 'false',  
  @rmtuser = 'LoginName',
  @rmtpassword = 'myPassword';

EXEC sp_serveroption 'LinkedServerName', 'rpc out', true;  

Agora, use o servidor vinculado para executar consultas usando nomes de quatro partes, até mesmo para criar uma nova tabela e inserir dados.

EXEC ('CREATE TABLE SchemaName.TableName(col1 int not null CONSTRAINT PK_col1 PRIMARY KEY CLUSTERED (col1) )') at LinkedServerName;  
EXEC ('INSERT INTO SchemaName.TableName VALUES(1),(2),(3)') at LinkedServerName; 

Consulte os dados usando nomes de quatro partes:

SELECT * FROM LinkedServerName.DatabaseName.SchemaName.TableName; 

H. Criar servidor vinculado da Instância Gerenciada de SQL com a autenticação de identidade gerenciada do Azure AD

Para criar um servidor vinculado com autenticação de identidade gerenciada, execute o T-SQL a seguir. O método de autenticação usa ActiveDirectoryMSI no @provstr parâmetro . Considere usar opcionalmente @locallogin = NULL para permitir todos os logons locais.

EXEC master.dbo.sp_addlinkedserver
@server     = N'MyLinkedServer',
@srvproduct = N'',
@provider   = N'MSOLEDBSQL',
@provstr    = N'Server=mi.35e5bd1a0e9b.database.windows.net,1433;Authentication=ActiveDirectoryMSI;';

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'MyLinkedServer',
@useself    = N'False',
@locallogin = N'user1@domain1.com';  

Se Instância Gerenciada de SQL do Azure identidade gerenciada (anteriormente chamada de identidade de serviço gerenciada) for adicionada como logon a uma instância gerenciada remota, a autenticação de Identidade Gerenciada será possível com o servidor vinculado criado como no exemplo anterior. Há suporte para identidades gerenciadas atribuídas pelo sistema e pelo usuário.

Se a identidade primária for definida, ela será usada; caso contrário, a identidade gerenciada atribuída pelo sistema será usada. Se a identidade gerenciada for recriada com o mesmo nome, o logon na instância remota também precisará ser recriado, pois a nova ID do aplicativo de identidade gerenciada e Instância Gerenciada SID da entidade de serviço não corresponderão mais. Para verificar se esses dois valores correspondem, converta SID em ID do aplicativo com a consulta a seguir.

SELECT convert(uniqueidentifier, sid) as AADApplicationID
FROM sys.server_principals
WHERE name = '<managed_instance_name>';

I. Criar servidor vinculado da Instância Gerenciada de SQL com a autenticação de passagem do Azure AD

Para criar um servidor vinculado com autenticação de passagem, execute o T-SQL a seguir.

EXEC master.dbo.sp_addlinkedserver
@server     = N'MyLinkedServer',
@srvproduct = N'',
@provider   = N'MSOLEDBSQL',
@datasrc    = N'mi.35e5bd1a0e9b.database.windows.net,1433';

Com a autenticação de passagem, o contexto de segurança do logon local é transferido para uma instância remota. A autenticação de passagem exige que a entidade de segurança do AAD seja adicionada como logon em Instância Gerenciada de SQL do Azure locais e remotos. Ambas as Instâncias Gerenciadas precisam estar em um Grupo de Confiança do Servidor. Quando os requisitos são atendidos, o usuário pode entrar em uma instância local e consultar a instância remota por meio do objeto de servidor vinculado.

Confira também