Algumas dicas para consultar e atualizar dados através de Linked Servers

Bom Dia Pessoal,

Estou aqui no trabalho aguardando uma restauração de uma base de 0,5TB por conta de um incidente na sexta-feira. Pelo tamanho acho que vai demorar bastante então resolvi postar alguns dicas para trabalhar com dados em Linked Servers. O objetivo não é explicar o que são, como configurar ou boas práticas acerca de Linked Server. Vou apenas passar duas dicas rápidas.

Em meu ambiente possui duas instâncias de SQL Server 2005. A primeira instância chama-se SQL2005 e a segunda instância chama-se YUKON (são instâncias nomeadas que vou utilizar). Para os exemplos criarei um login chamado UsrLoginLK na instância SQL2005 e um Linked Server na instância Yukon que permita o acesso na instância SQL2005 a partir desse login. Para evitar ter de conceder permissões irei adicionar o login UsrLoginLK como sysadmin (por razões de segurança não façam isso em ambientes de produção).

— Cria um login para ser acessado remotamente
CREATE LOGIN UsrLoginLK WITH Password = ‘PWD123456’

— Concede permissões administrativas ao novo login
EXEC sp_addsrvrolemember ‘UsrLoginLK’,‘sysadmin’

Na instância YUKON é criado o Linked Server para a instância SQL2005.

— Configura o Linked Server para acessar a instância SQL2005
EXEC master.dbo.sp_addlinkedserver @server = ‘.\SQL2005’, @srvproduct=‘SQL Server’
EXEC master.dbo.sp_serveroption @server=‘.\SQL2005’, @optname=‘data access’, @optvalue=‘true’
EXEC master.dbo.sp_serveroption @server=‘.\SQL2005’, @optname=‘rpc’, @optvalue=‘true’
EXEC master.dbo.sp_serveroption @server=‘.\SQL2005’, @optname=‘rpc out’, @optvalue=‘true’

— Configura um login para acessar o Linked Server
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = ‘.\SQL2005’,
    @locallogin = NULL , @useself = ‘False’,
    @rmtuser = ‘UsrLoginLK’, @rmtpassword = ‘PWD123456’
GO

Uma das formas mais disseminadas é o acesso a um Linked Server através do FQN (Full Qualified Name) baseado em quatro partes (Servidor.Banco.Schema.Objeto). A consulta abaixo consulta dados através do Linked Server recém criado.

— Obter informações sobre os bancos existentes no Linked Server
SELECT Name, State_Desc FROM [.\SQL2005].Master.SYS.Databases

Outra alternativa igualmente utilizada é através do comando OPENQUERY. Ela é inclusive muito utilizada quando as fontes de dados não são SQL Server e podem não aceitar o FQN baseado em quatro partes.

— Obter informações sobre os bancos existentes no Linked Server
SELECT * FROM OPENQUERY([.\SQL2005],‘SELECT Name, State_Desc FROM sys.Databases’)

Na grande maioria dos casos, utilizamos Linked Servers para realizar consultas e quase sempre estatáticas. Entretanto, quando as coisas se tornam um pouco mais dinâmicas a utilização de Linked Servers deixa de ser tão trivial assim. Suponha que uma procedure tenha como parâmetros o nome de um banco, de um schema e de uma tabela e com base nesses parâmetros tenha de executar uma consulta contra um Linked Server. As coisas podem não sair bem como o esperado.

— Declara o nome do banco de dados, o schema e o objeto para consulta
DECLARE @Banco VARCHAR(20), @Schema VARCHAR(20), @Objeto VARCHAR(20)
SET @Banco = ‘Northwind’
SET @Schema = ‘dbo’
SET @Objeto = ‘Customers’

Como o parâmetro da instrução OPENQUERY trata-se de uma string é bem comum pensar que ela pode ser dinâmica. Assim sendo as tentativas abaixo seriam um primeiro impulso.

SELECT * FROM OPENQUERY([.\SQL2005],
    ‘SELECT * FROM ‘ + @Banco + ‘.’ + @Schema + ‘.’ + @Objeto)

DECLARE @cmdSQL VARCHAR(100)
SET @cmdSQL = ‘SELECT * FROM ‘ + @Banco + ‘.’ + @Schema + ‘.’ + @Objeto
SELECT * FROM OPENQUERY([.\SQL2005],@cmdSQL)

Embora factíveis, o TSQL não permite esse tipo de construção. As instruções acima geram um erro e não podem ser executadas. A solução mais imediata seria tornar tudo dinâmico. Ex:

DECLARE @cmdSQL VARCHAR(200)
SET @cmdSQL = ‘SELECT * FROM ‘ + @Banco + ‘.’ + @Schema + ‘.’ + @Objeto
SET @cmdSQL = ‘SELECT * FROM OPENQUERY([.\SQL2005],’ +
    ”” + @cmdSQL + ”” + ‘)’

No SQL Server 2000 essa era praticamente a única solução possível. Embora seja uma boa solução, problemas podem aparecer se a instrução SQL repassada para o linked server trabalhar com aspas e (ou) haja a necessidade de se realizar um join com outras tabelas. O SQL Server 2005 disponibiliza um recurso novo no TSQL para facilitar esse tipo de consulta. A mesma poderia ser reescrita da seguinte forma:

DECLARE @cmdSQL VARCHAR(200)
SET @cmdSQL = ‘SELECT * FROM ‘ + @Banco + ‘.’ + @Schema + ‘.’ + @Objeto
EXEC (@cmdSQL) AT [.\SQL2005]

No SQL Server 2005, basta montar o comando dinamicamente e utilizar o operador AT para informar o Linked Server que se deseja executar a consulta.

Outra situação que pode acontecer é a necessidade de se realizar uma atualização via Linked Server. Podemos por exemplo receber uma parâmetro e necessitar atualizar uma determinada linha em um Linked Server. Adaptemos esse exemplo para o Northwind.

DECLARE @City NVARCHAR(15), @CustomerID NCHAR(5)
SET @City = ‘Belo Horizonte’
SET @CustomerID = ‘COMMI’

A necessidade é atualizar o nome da cidade para Belo Horizonte onde o CustomerID seja igual a ‘COMMI’. Esse é o tipo de necessidade que é melhor endereçada através de stored procedures remotas, mas no caso de não haver essa possibilidade, podemos utilizar um truque.

— Verifica o registro antes da alteração
SELECT * FROM [.\SQL2005].Northwind.dbo.Customers
WHERE CustomerID = @CustomerID

— Monta uma CTE
;WITH Customers AS (
SELECT * FROM [.\SQL2005].Northwind.dbo.Customers
WHERE CustomerID = @CustomerID)

— Atualiza o registro referenciando a CTE
— O WHERE pode ser dispensado, pois, a CTE já fez o filtro
UPDATE Customers SET City = @City WHERE CustomerID = @CustomerID

— Verifica o novo valor do registro
SELECT * FROM [.\SQL2005].Northwind.dbo.Customers
WHERE CustomerID = @CustomerID

Após a execução do código, percebe-se que a cidade foi trocada de São Paulo para Belo Horizonte. Esse código montou uma tabela virtual chamada Customers e atualizou o registro. Quando isso ocorre, o UPDATE é redirecionado para a tabela de origem (no caso a do Linked Server) já que a CTE apenas seleciona os dados, mas não os contém de fato. No SQL Server 2000 o mesmo poderia ser feito (com as devidas adaptações) através de Views.

O código funciona, mas como eu havia dito optar por procedures remotas pode ser mais efetivo principalmente por questões de segurança. Outra alternativa válida é a utlização do EXEC com o AT.

DECLARE @City NVARCHAR(15), @CustomerID NCHAR(5), @cmdSQL VARCHAR(200)
SET @City = ‘Belo Horizonte’
SET @CustomerID = ‘COMMI’
SET @cmdSQL = ‘UPDATE Northwind.dbo.Customers SET City = ‘ +
    ”” + @City + ”” + ‘ WHERE CustomerID = ‘ +
    ”” + @CustomerID + ””

EXEC (@cmdSQL) AT [.\SQL2005]

Acredito que essas dicas podem vir a ser muito úteis para quem trabalha com Linked Servers no SQL Server 2005.

[ ]s,

Gustavo

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s