sp_addlinkedserver (Transact-SQL)

Aplica-se a: simSQL Server (todas as versões compatíveis) SimInstância Gerenciada do Azure SQL

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 estiver definido como uma instância do SQL Server, poderão ser executados procedimentos armazenados remotos.

Ícone de link do 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 =] ' servidor '

É 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 de OLE DB especificado instalado no computador atual. O valor provider_name é nvarchar (128), com um padrão de NULL; no entanto, se provider_name for omitido, sqlncli será usado.

Observação

Usar SQLNCLI será redirecionado SQL Server para a versão mais recente do SQL Server provedor de OLE DB de cliente nativo. Espera-se que o provedor OLE DB seja registrado com o PROGID especificado fornecido no Registro.

Importante

O Microsoft OLE DB Provider para SQL Server (SQLOLEDB) anterior e o provedor SQL Server Native Client OLE DB (SQLNCLI) permanecem preteridos e não é recomendável usar nenhum dos dois 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.

[ @datasrc =] ' data_source '

É o nome da fonte de dados conforme interpretada pelo provedor OLE DB. O valor data_source é nvarchar ( 4000 ). data_source é passado como a propriedade DBPROP_INIT_DATASOURCE para inicializar o provedor de OLE DB.

[ @location =] ' local '

É 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 de 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 parâmetro provstr é passado para IDataInitialize ou definido como a propriedade DBPROP_INIT_PROVIDERSTRING para inicializar o provedor de OLE DB.

Quando o servidor vinculado é criado no SQL Server provedor de OLE DB nativo do cliente, a instância pode ser especificada usando a palavra-chave do servidor como SERVER=servername\\instancename para especificar uma instância específica do SQL Server . O 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 parâmetro @ parâmetro provstr ou @ Catalog . Opcionalmente, a cadeia de conexão também pode fornecer um nome de parceiro de failover.

[ @catalog =] ' Catálogo '

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

Fonte de dados remota OLE DB. Provedor de OLE DB product_name provider_name data_source local provider_string catalog
SQL Server MicrosoftSQL ServerProvedor de OLE DB de cliente nativo SQL Server 1 (padrão)
SQL Server MicrosoftSQL ServerProvedor de OLE DB de cliente nativo SQLNCLI Nome de rede do SQL Server (para instância padrão) Nome do banco de dados (opcional)
SQL Server MicrosoftSQL ServerProvedor de OLE DB de cliente nativo SQLNCLI nome do Server \ InstanceName (para 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 Microsoft a documentação do provedor de OLE DB para DB2. 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 Microsoft SQL Server provedor de OLE DB de cliente nativo é o provedor usado com o 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 de data_source, local, provider_string e Catálogo identificam o banco de dados ou os bancos que o servidor vinculado aponta para. 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 armazenado sp_addlinkedserver não pode ser executado em uma transação definida pelo usuário.

Importante

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

Importante

Quando um servidor vinculado é criado usando sp_addlinkedserver o, um mapeamento automático padrão é adicionado a todos os logons locais. Para não SQL Server provedores, os SQL Server logons autenticados podem ser capazes de obter acesso ao provedor na conta de SQL Server serviço. 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 vinculado Server . (O SQL Server Management Studio A caixa de diálogo novo servidor vinculado é implementada de uma maneira que requer associação na sysadmin função de servidor fixa.)

Exemplos

a. usar o provedor de OLE DB de 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 vinculado S1_instance1 em uma instância do SQL Server usando o SQL Server Driver OLE DB.

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

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

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 do 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

este exemplo pressupõe que o Microsoft acesso e o banco de dados de exemplo Northwind estão instalados e que o Northwind banco de dados reside no C:\Msoffice\Access\Samples no mesmo servidor que a instância de 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 de 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 Microsoft provedor de OLE DB 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 do provedor do Microsoft OLE DB para Excel

para criar uma definição de servidor vinculado usando o Microsoft provedor de OLE DB para Jet para acessar uma planilha Excel no formato 1997-2003, primeiro crie um intervalo nomeado em Excel especificando as colunas e linhas da planilha de Excel para selecionar. 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';  

E. Use o provedor do Microsoft OLE DB 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 Jet Mecanismo de Banco de Dados.

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';  

Configure 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 de nuvem. Este é um componente para soluções híbridas de banco de dados que abrangem redes corporativas locais e a nuvem do Azure.

O SQL Server produto box contém o recurso de consulta distribuída, que permite que você grave consultas para combinar dados de fontes de dados locais e dados de fontes remotas (incluindo dados de fontes que não são de SQL Server dados) definidos como servidores vinculados. Cada Banco de Dados SQL do Azure (exceto o banco de dados do servidor lógico master ) 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 o Banco de Dados SQL do Azure incluem capacidade de gerenciamento, alta disponibilidade, escalabilidade, trabalhando com um modelo familiar de desenvolvimento, e um modelo de dados relacionais. Os requisitos de seu aplicativo de banco de dados determinam como ele usaria o Banco de Dados SQL do Azure na nuvem. Você pode mover todos os dados imediatamente para o Banco de Dados SQL do Azure, ou mover progressivamente alguns de seus dados, mantendo os demais dados no local. Para esse aplicativo de banco de dados híbrido, Banco de Dados SQL do Azure agora pode ser adicionado como servidores vinculados e o aplicativo de banco de dados pode emitir consultas distribuídas para combinar dados de Banco de Dados SQL do Azure fontes de dados locais e.

Veja 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 de uso.

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

Adicionar 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, 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 opcionalmente usar @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 o Azure SQL Instância Gerenciada identidade gerenciada (anteriormente chamada de identidade de serviço gerenciada) for adicionada como logon em 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 as identidades gerenciadas atribuídas ao sistema e ao usuário.

Se a identidade primária estiver 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, porque a nova ID de aplicativo de identidade gerenciada e a Instância Gerenciada SID de entidade de serviço não correspondem mais. Para verificar se esses dois valores correspondem, converta SID para 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'Server=mi.35e5bd1a0e9b.database.windows.net,1433';

Com a autenticação de passagem, o contexto de segurança do logon local é transportado para uma instância remota. a autenticação de passagem exige que a entidade de segurança de AAD seja adicionada como logon no Instância Gerenciada de SQL do Azure local e remoto. Ambas as instâncias gerenciadas precisam estar em um grupo de confiança do servidor. Quando os requisitos forem atendidos, o usuário poderá entrar em uma instância local e consultar a instância remota por meio do objeto de servidor vinculado.

Confira também