Dicas Técnicas BufaloInfo

SQL Server : Pesquisar por uma string em todos os campos de uma tabela

SQL Server : Exportando dados para o excel

Frequentemente precisamos fazer exportações de dados para o excel. É claro que podemos fazer essas exportações utilizando o DTS ou o atual SSIS. Mas que tal fazer a exportação utilizando uma instrução SQL, mais fácil de ser disparada do que os pacotes de exportação ?

A função OpenRowSet permite que sejam feitos acessos a outros bancos de dados mesmo que estes não estejam cadastrados como linked servers. Funciona como um linked server inline.

Desta forma podemos fazer uma exportação para o excel em uma única instrução, veja :

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\teste.xls;User=Admin;Password=',
'SELECT ID, Empresa FROM [Sheet1$]')
SELECT customerid,companyname from customers

Algumas observações :

A planilha do excel (no exemplo teste.xls) tem que já existir com os cabeçalhos de campo, do contrário ocorre um erro

Permissões no diretório temporário do usuário do serviço do sql server/sql server agent são necessárias. Os seguintes links ajudam no caso de ocorrerem erros estranhos : http://support.microsoft.com/kb/814398/en-us e http://support.microsoft.com/kb/296711/EN-US/

SQL Server : Invertendo o valor de um campo bit

Uma dica simples, mas que pode ser útil : Como inverter o valor de um campo bit ? Fazer com que verdadeiro seja falso e vice versa ?

Uma forma simples de fazer isso é com manipulação binária. O SQL Server tem o operador ^ que representa um OR exclusivo binário.

Veja um exemplo :

update testedicas set valor = 1^valor

O é basicamente um bit 1. Como estamos usando um OR exclusivo, se os dois forem 1, o resultado é 0, se valor for 0, permanece ligado o bit 1, desta forma causando a inversão no campo bit.

SQL Server : Passando multiplos registros como parâmetro para procedure

Na dica em http://www.bufaloinfo.com.br/dicas.asp?cod=306 foi mostrado com realizar esse truque utilizando dados em XML e a instrução OpenXML.

Porém, a partir do SQL Server 2005 ficou bem mais simples de realizar esta tarefa com os recursos específicos de manipulação de XML existentes no SQL Server.

Veja um exemplo :

declare @xml xml
select @xml='<clientes>
<cliente><customerid>ALFKI</customerid></cliente>
<cliente><customerid>ANATR</customerid></cliente>
<cliente><customerid>ANTON</customerid></cliente>
</clientes>'

SELECT
clientes.cliente.value('customerid[1]','nvarchar(30)')
As Customerid
FROM
@xml.nodes('/clientes/cliente') clientes(cliente)

O método nodes na variável do tipo XML nos permite acessar os elementos do XML como sendo uma tabela. No caso o nome da tabela, para esta query, fica sendo "clientes" e o campo XML retornado pelo nodes fica sendo "cliente". Então cada cliente se torna um registro

Na lista de campos, temos o campo cliente, xml, sobre o qual podemos utilizar o método value para extrairmos a informação que desejamos, sabendo que neste ponto já estamos lidando com apenas um cliente, que foi separado pelo método nodes. Na XQuery ainda precisamos indexar o elemento que desejamos, mas nesse caso o indice será sempre [1]

Desta forma podemos passar um XML para nossa stored procedure e a procedure poderá retirar do XML os dados de que necessita.

SQL Server : Eliminando linhas duplicadas com campo Text

Já publiquei aqui antes dicas para a eliminação de linhas duplicadas em tabelas do SQL Server. Porém quando a tabela possui um campo Text a coisa complica.

O campo Text não pode ser usado junto de um Distinct, tendo também muitas limitações de uso em subQuerys. Não pode haver uma variável text. Usar varchar como intermediário corta o texto para 8000.

Então como eliminar as linhas duplicadas ?

Simples, recorrendo ao velho cursor :

declare x cursor for select codigo from tabela order by codigo desc
declare @velhocodigo int
declare @codigo int
select @velhocodigo=0
open x
fetch next from x into @codigo
while @@fetch_status=0
begin
if @codigo=@velhocodigo
begin
delete tabela where current of x
end
else
begin
select @velhocodigo=@codigo
end
fetch next from x into @codigo
end
close x
deallocate x

Não é uma tatica bonitinha ou de excelente performance, mas quando tudo o mais falha, como no caso da presença de campos text, o cursor acaba nos salvando

SQL Server : Simplificando a exportação de dados

Especialmente quando se trabalha com a área de análise de dados para decisões na empresa é muito comum a necessidade de obtenção de alguns relatórios bem personalizados.

Em alguns casos, já encontrei empresas nas quais era bem produtivo deixar que os próprios usuários da área de decisão tivessem acesso a uma cópia da base de dados e pudessem realizar suas próprias querys.

Uma forma de simplificar este trabalho é configurar a janela de resultados das querys para já mostrar os dados em formato de exportação, desta forma permitindo que o resultado seja salvo em .CSV e aberto no excel.

Trata-se de uma tarefa muito simples : No SQL Server Management Studio entra-se em Tools->Options->Query Results->SQL Server->Results to text altera-se então a configuração output format para "comma delimited".

Pode-se ainda, no item Query Results, alterar a "Default destination for results" para "results to text".

SQL Server : Recuperando um banco de dados depois de uma perda de log no SQL 2005

Na dica em http://www.bufaloinfo.com.br/dicas.asp?cod=255, publicada há alguns anos, é mostrado como fazer a recuperação de um banco de dados do SQL Server 2000 que tenha perdido seu arquivo de log mas esteja com recovery model full.

Ocorre que no SQL Server 2005 esta dica não é mais válida, devido a diversas mudanças ocorridas no servidor.

Então : Como recuperar um banco que possui recovery model full mas perdeu seu arquivo de log, .LDF, possuindo apenas seu arquivo .MDF ?

Primeiramente, precisamos colocar o banco em modo de emergência e single user :

alter database nomeBanco set Emergency, Single_User

Feito isso, fazemos a recuperação :

DBCC CheckDB('nomeBanco',REPAIR_ALLOW_DATA_LOSS)

Por fim o banco ainda estará em modo single user, então voltamos ele ao modo multi-user :

alter database nomebanco set multi_user

Pronto, o banco estará de volta ao ar. É claro que só se faz isso em último caso, no caso do log realmente estar perdido, pois este procedimento pode gerar quebra de integridade nos dados, exatamente devido a perda do log que é o responsável por isso.

Juntando tudo :

alter database nomeBanco set Emergency, Single_User
DBCC CheckDB('nomeBanco',REPAIR_ALLOW_DATA_LOSS)
alter database nomebanco set multi_user

SQL Server : Cuidado com NULLs em sub-queries

Observem estes selects abaixo. Parecem não ter segredo nenhum a esconder, correto ?

select count(*) from pessoas where email in (select email from lista)
select count(*) from pessoas where email not in (select email from lista)

Vamos considerar o seguinte :

Pessoas : 200 registros
Lista : 1000 registros

Agora o resultado :

1a) 70
2a) 0

Então a pergunta : Onde foram parar 140 registros ?

Este problema curioso mas perfeitamente normal pode com certeza fundir a cabeça de muitos programadores que não lembrem do significado do valor NULL em um banco de dados.

NULL : Identifica um valor desconhecido, o valor pode existir ou não no mundo real, mas não se tem conhecimento de seu valor nos dados do sistema.

Eis ai o segredo oculto, o significado do NULL : Existindo um valor NULL na tabela LISTA, a 1a query consegue ser respondida, indicando os registros que estão na tabela lista, mas a 2a query tem um resultado inviável : Como dizer quantos registros não estão lá se existem valores NULL ? Se você levar em consideração o significado do NULL, verá que não há como saber quantos e-mails não estão na lista, simplesmente porque existem valores na lista que são desconhecidos. De fato, se levarmos ao pé da letra nem o valor da 1a query é preciso, poderiam haver mais de 70 pessoas na lista.

SQL Server : Cuidado com os hints de lock

Observem o seguinte select :

set @novoSeqCartao=(select isnull(max(cartao),0) +1
from cartao (holdlock rowlock updlock)
where numAgencia=@numAgencia and
numConta=@numConta)

Claro que esse select é só um pedaço de uma stored procedure maior, mas é fácil perceber que está fazendo a obtenção do próximo valor de um número sequencial.

Porém quando foi rodado gerou uma grande surpresa para o desenvolvedor : ao invés de lockar apenas alguns registros, de acordo com a clausula where, fez lock na tabela inteira.

Depois de muito procurar identificou-se o problema : Por não haver indice em numAgencia e numConta o select precisou fazer um table scan e, fazendo isso, lockou todos os registros envolvidos, mesmo os que não atendiam a clausula where. Como eram muitos, mudou o lock de rowlock para tablelock.

Cuidado com os hints !

Dica obtida no blog de Gilberto Uchoa - http://br.thespoke.net/BlogReader/SingleEntry.aspx?ID=11898

SQL Server : Salvando scripts automaticamente no EM

No artigo em http://www.bufaloinfo.com.br/artigos/artigo3111.asp falamos sobre métodos para controlar versão de objetos no banco, tal como manter o script destes objetos arquivado.

O Enterprise Manager gera estes scripts para nós, mas devemos lembrar de salvar tais scripts. Se em um momento de distração esquecermos teremos problemas com o versionamento.

Para evitar pequenos descuidos podemos configurar o enterprise manager para salvar automaticamente os scripts. Isso fará com que sempre que ocorrer uma alteração em um objeto o enterprise manager automaticamente peça o nome do arquivo com o qual deve gravar o script.

Para fazermos isso basta, após uma alteração, quando você clicar em salvar script, marcar a checkbox "automaticaly generate save script on every save"

Uma alternativa é ir direto na chave de registry :

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\DataTools\AutoSaveChangeScript

Basta definir esta chave do registry como 1 e o Enterprise Manager sempre pedirá o nome do arquivo para salvar o script de alteração nas tabelas.