SQLBURGER

Atualização do cache do Intellisense–Microsoft SQL Server

2013-03-14T15:01:00.001-03:00

Esta é uma dica rápida pra quem tem dificuldade em trabalhar com o Intellisense do SQL Server. Para este exemplo, estou usando a versão 2008R2, então creio que isso se aplique a todas as versões a partir da 2005.

Quando você cria uma tabela, ou altera a estrutura de uma tabela existente (adicionando uma nova coluna), o intellisense parece não reconhecer estas modificações, e atrapalha bastante durante a criação de um código.

Muitos desabilitam o intellisense exatamente por não compreender o comportamento da ferramenta, porém ela é muito útil.

A solução? Muito simples.

Se você utiliza a configuração de teclado padrão do SQL Server 2005 ou superior, basta pressionar as teclas CTRL + SHIT + R. Caso você utilize a configuração de teclado compatível com versão 2000 e anteriores, esta tecla de atalho não irá funcionar. Mas aí basta você acessar o menu Editar – Intellisense – Atualizar Cache Local:

E problema resolvido! Cada vez que fizer alterações nas estruturas, criar novos procedimentos, tabelas, e o intellisense teimar com você dizendo que está errado, você já sabe como resolver a bronca.

Fazer update em outro banco de dados da mesma instância

2013-01-06T16:34:00.001-02:00

As vezes, é necessário realizar operações de dados entre diferentes bancos de dados dentro de uma instância SQL. Na verdade, isso é muito comum. Então aqui vai a dica.

1ª opção: Buscando os dados de um outro banco de dados ao qual você está contectado:

  update ocrd
     set ocrd.cardname = ocrd2.cardname
    from sboatlantida..ocrd ocrd2
   where ocrd.cardcode = ocrd2.cardcode

Neste exemplo, especificamos a tabela que será atualizada logo após o update, um alias para a tabela do banco onde vamos pegar os dados (ocrd2) e na clausula where, fazemos a correlação entre as tabelas.

2ª opção: Realizando update em outro banco de dados:

  update ocrd1
     set ocrd1.cardname = ocrd2.cardname
    from atl..ocrd ocrd1
   inner join SBOAtlantida..ocrd ocrd2
      on ocrd1.cardcode = ocrd2.cardcode

Já este exemplo, estamos atualizando o banco de dados ATL através de dados provenientes do banco SBOAtlantida. Para iniciantes, pode parecer estranho especificar na clausula FROM (da onde) qual a tabela a ser atualizada. Mas entenda como a clausula FROM onde especificamos o nome das tabelas, independente da qual será atualizada. Este segundo modelo pode ser utilizado em ambos os casos descritos acima.

Muito útil quando precisa interligar bases de diferentes sistemas.

Tamanho de todas as tabelas de todos os bancos de dados de uma instância SQL Server

2013-01-02T19:51:00.001-02:00

Caros colegas, hoje recebi um comentário que acabou se tornando uma sugestão bem interessante, visto que recebo muitas visitas sobre como obter o tamanho das tabelas de um banco. Bom, a solução que vou propor aqui vai mais a fundo, de forma a proporcionar uma ferramenta que facilite a gestão do tamanho de tabelas, quando isto é necessário. E é claro, eu preciso abrir 2013 com um post bacana, então aí vai!

É claro, como profissional da área, não posso negligenciar uma verdade: se você está tendo que monitorar tamanhos de tabelas no banco de dados, existem problemas bem mais graves por trás de tudo isso. Ficar resolvendo somente o resultado de um problema não resolve o problema em si, tem que atacar a causa, sempre. Nem preciso entrar em detalhes, certo? Mas se você não consegue resolver a fonte do problema (ex.: sistema de terceiros), essa dica vai facilitar sua vida.

Hoje vou postar duas alternativas bem simples para melhorar a utilização do cáculo, uma derivada da outra. A primeira procedure, que eu acho a mais interessante, realizará o envio automático ao seu e-mail com um relatório em HTML com as maiores tabelas, de todos os bancos de dados, sendo acionada por uma JOB, por exemplo. Eu já fiz uma formatação HTML que permita visualizar em dispositivos móveis (claro, qualquer aparelho que reproduza ao menos HTML, por favor). A segunda é o mesmo cálculo, porém uma procedure simplificada, para ser executada manualmente e que retorna uma tabela com todos os registros por linha, para visualizar no resultado da query, ou copiar e colar para uma planilha, por exemplo.

* Importante

- ambas as procedures podem ser salvas e executas em qualquer banco de dados, isso é indiferente.

- Para a utilização do primeiro modelo, será necessário conhecimento em JOBS (criar job) e DATABASE MAIL (serviço ativo e conta SMTP devidamente configurada).

Então vamos começar pelo procedimento que envia o relatório por email. Observe os comentários no código para melhores orientações sobre como personalizar a execução de acordo com suas necessidades:

-- exec sp_espacotabelabanco_email
create proc sp_espacotabelabanco_email
as
 
/*
  Fabiano Cores 02.01.2013
  http://sqlburger.blogspot.com
  Segunda versão do cálculo do tamanho das tabelas, que trata todos os bancos da instância onde 
  for executada.
  
  Versão 1.: Envio do relatório para um email pré-determinado
  
*/
 
  set nocount on

  declare @vname   varchar(100)
  declare @vbase   varchar(100)
  declare @sql     varchar(max)
  declare @email   varchar(max)
  declare @assunto varchar(200)
  declare @vtexto  varchar(max)
  
  create table #tmpTamTabela (
    db         varchar(100) null
  , name       varchar(100) null
  , rows       int          null
  , reserved   varchar(25)  null
  , data       varchar(25)  null
  , index_size varchar(25)  null
  , unused     varchar(25)  null )
  
  create table #tmpTabelas (
    tabela varchar(100) null
  )
  
  create table #retorno (
    texto varchar(max) not null
  , ordem int identity
  )
  
  insert into #retorno (texto)
    select '<table border=1 cellpadding=2 cellspacing=0 width=100%>'

  insert into #retorno (texto)
    select '<tr bgcolor=#000000>'
         + '  <td align=left  Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Tabela</b></font></td>'
         + '  <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Linhas</b></font></td>'
         + '  <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Tamanho</b></font></td>'
         + '  <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Dados</b></font></td>'
         + '  <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Index</b></font></td>'
         + '  <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Livre</b></font></td>'
         + '</tr>'
             
  declare cp0 cursor local fast_forward read_only for
    select name
     from master..sysdatabases
    /* ESPECIFIQUE AQUI AS EXCEÇÕES PARA CÁLCULO */ 
    where name not in ('master', 'tempdb', 'model', 'msdb')
    order by name
  
  open cp0
  
  while 0 =0 
  begin
    fetch next from cp0 into @vbase
    if @@fetch_status <> 0 break
    
    truncate table #tmpTabelas
    
    insert into #retorno (texto)
      select '<tr bgcolor=#900000><td colspan=6><font face=tahoma size=2 color=#ffffff>DB: <b>' + rtrim(@vbase) + '</b></td></tr>'
    
    select @sql = '
        
          insert into #tmpTabelas (tabela)
            select name
              from [' + rtrim(@vbase) + ']..sysobjects
             where type = ''U''
            
            '
    
    exec(@sql)
      
    declare cp1 cursor local fast_forward read_only for
      select tabela from #tmpTabelas
       order by 1
   
    open cp1
   
    while 1 = 1
    begin
      fetch next from cp1 into @vname
      if @@fetch_status <> 0 break
      
      select @sql = '
        
          insert into #tmpTamTabela (name, rows, reserved, data, index_size, unused)
            exec [' + rtrim(@vbase) + ']..sp_spaceused ''' + rtrim(@vname) + '''
            
          update #tmpTamTabela
             set db = ''' + rtrim(@vbase) + '''
           where db is null
            
            '
      
      exec(@sql)
      
    end
    close cp1
    deallocate cp1    
    
    insert into #retorno (texto)
      /* altere aqui a quantidade de tabelas a ser exibida no relatório */
      select top 10 '<tr>'
           + '  <td align=left Valign=top><font face=tahoma size=1>' + rtrim(name) + '</font></td>'
           + '  <td align=right Valign=top><font face=tahoma size=1>' + rtrim(rows) + '</font></td>'
           + '  <td align=right Valign=top><font face=tahoma size=1>' + rtrim(reserved) + '</font></td>'
           + '  <td align=right Valign=top><font face=tahoma size=1>' + rtrim(data) + '</font></td>'
           + '  <td align=right Valign=top><font face=tahoma size=1>' + rtrim(index_size) + '</font></td>'
           + '  <td align=right Valign=top><font face=tahoma size=1>' + rtrim(unused) + '</font></td>'
           + '</tr>'
        from #tmpTamTabela
       where db = @vbase
       order by convert(int, replace(reserved, ' KB','')) desc  

  end
  close cp0
  deallocate cp0
  
  insert into #retorno (texto)
    select '</table>'

  declare cp2 cursor local fast_forward read_only for
    select texto from #retorno order by ordem
    
  open cp2
  
  while 1 = 1
  begin
    fetch next from cp2 into @vtexto
    if @@fetch_status <> 0 break
    
    select @email = isnull(@email, '') + rtrim(@vtexto)
    
  end
  close cp2
  deallocate cp2
  
  set @assunto = 'Relatório tabelas - ' + rtrim(@@SERVERNAME)
  
  exec msdb.dbo.sp_send_dbmail @profile_name           =  'atlanta',
                               @recipients             =  'fbcores@gmail.com',
                               @copy_recipients        =  'fabiano.cores@nms.com.br', 
                               @blind_copy_recipients  =  '', 
                               @subject                =  @assunto,
                               @body                   =  @email,
                               @body_format            =  'HTML'

Não vou entrar em detalhes em como se utiliza a função de email ou como se cria uma JOB no SQL. Creio que sejam assuntos para outro artigo. Existem muito conteúdo explicativo sobre como ativar e utilizar o DATABASE MAIL e o SQL SERVER AGENT, basta dar uma procurada na internet. Mas se tiverem dificuldades em concluir essa configuração, me avisem pra eu saber se é necessário ou não fazer um artigo específico para ensinar como utilizar essas ferramentas.

A imagem abaixo é a visualização do relatório no meu tablet. No celular também ficou bem legal:

O segundo exemplo, é uma versão simplificada do procedimento, que não trata a formatação de texto nem nada parecido. É uma procedure para ser executada manualmente sempre que o administardor quiser consultar os tamanhos das tabelas:

-- exec sp_espacotabelabanco
create proc sp_espacotabelabanco
as
 
/*
  Fabiano Cores 02.01.2013
  http://sqlburger.blogspot.com
  Segunda versão do cálculo do tamanho das tabelas, que trata todos os bancos da instância onde 
  for executada.
  
  Versão 2: simplificada, para execução manual
  
*/
 
  set nocount on

  declare @vname   varchar(100)
  declare @vbase   varchar(100)
  declare @sql     varchar(max)
  
  create table #tmpTamTabela (
    db         varchar(100) null
  , name       varchar(100) null
  , rows       int          null
  , reserved   varchar(25)  null
  , data       varchar(25)  null
  , index_size varchar(25)  null
  , unused     varchar(25)  null )
  
  create table #tmpTabelas (
    tabela varchar(100) null
  )
  
  declare cp0 cursor local fast_forward read_only for
    select name
     from master..sysdatabases
    /* ESPECIFIQUE AQUI AS EXCEÇÕES PARA CÁLCULO */ 
    where name not in ('master', 'tempdb', 'model', 'msdb')
    order by name
  
  open cp0
  
  while 0 =0 
  begin
    fetch next from cp0 into @vbase
    if @@fetch_status <> 0 break
    
    truncate table #tmpTabelas
    
    select @sql = '
        
          insert into #tmpTabelas (tabela)
            select name
              from [' + rtrim(@vbase) + ']..sysobjects
             where type = ''U''
            
            '
    
    exec(@sql)

    declare cp1 cursor local fast_forward read_only for
      select tabela from #tmpTabelas
       order by 1
   
    open cp1
   
    while 1 = 1
    begin
      fetch next from cp1 into @vname
      if @@fetch_status <> 0 break
      
      select @sql = '
        
          insert into #tmpTamTabela (name, rows, reserved, data, index_size, unused)
            exec [' + rtrim(@vbase) + ']..sp_spaceused ''' + rtrim(@vname) + '''
            
          update #tmpTamTabela
             set db = ''' + rtrim(@vbase) + '''
           where db is null
            
            '
      
      exec(@sql)
      
    end
    close cp1
    deallocate cp1    
    
  end
  close cp0
  deallocate cp0
  
  select db as 'Banco'
       , name as 'Nome'
       , rows as 'Linhas'
       , convert(int, replace(reserved, ' KB','')) as 'Tamanho total'
       , convert(int, replace(data, ' KB',''))as 'Dados'
       , convert(int, replace(index_size, ' KB',''))as 'Index'
       , convert(int, replace(unused, ' KB',''))as 'Não utilizado'
    from #tmpTamTabela
   order by db, convert(int, replace(reserved, ' KB','')) desc

Esta procedure acima retorna o resultado da seguinte forma:

Infelizmente não estou tendo tempo para escrever mais artigos, mas espero ter ajudado com este, tentei simplificar o máximo possível, só copiar, colar e desfrutar.

E feliz 2013!

Round() – Arredondar ou truncar os decimais?

2012-02-25T04:19:00.001-02:00

Esta dica vai para todos os colegas que trabalham com SQL Server e precisam trabalhar arredondamentos. Creio que a maioria dos programadores sql conhecem a função ROUND() que faz o arredondamento de casas decimais de um determinado número. Porém, há alguns dias atrás, precisei de um resultado um pouco diferente… como estamos alterando algumas funções que envolvem a emissão de nota fiscal eletrônica, o tratamento aplicado nos valores exigiu uma extensão do comando ROUND() que talvez muita gente não conheça, mas extremamente útil.

Em uma nova fiscal eletrônica, todos os valores unitários devem conter 4 casas decimais. Porém os totais e valores de impostos devem ser apenas com 2 casas. E tem mais um detalhe: na multiplicação da quantidade pelo valor unitário, o valor total não deve ser arredondado, e sim ter seus decimais “truncados”.

Exemplo:

original = 2,4567
arredondado = 2,46
truncado = 2,45

Já vi muitos códigos inusitados para realizar esta tarefa. Funções cabulosas e cheias de “if”. Mas aqui vai a dica, vamos entender melhor o comando round():

- Sintaxe

ROUND (numeric_expression , length [ ,function ] )

- Argumentos

numeric_expression

É uma expressão da categoria de tipo de dados numéricos exatos ou aproximados, com exceção do tipo de dados bit.

length

É a precisão à qual a numeric_expression deve ser arredondada. length deve ser uma expressão do tipo tinyint, smallint ou int. Quando length for um número positivo, numeric_expression é arredondada para o número de posições decimais especificado por length. Quando length for um número negativo, numeric_expression é arredondada no lado esquerdo do ponto decimal, conforme especificado por length.

function

É o tipo de operação a ser executada. function deve ser tinyint, smallintou int. Quando function é omitido ou tem um valor 0 (padrão), a numeric_expression é arredondada. Quando um valor diferente de 0 é especificado, numeric_expression é truncado.

(fonte: msdn)

Veja um exemplo abaixo:

  declare @vl decimal(19,9) 
   
  select @vl = 2.4567 
   
  select convert(decimal(12, 2), round(@vl, 2)) as 'arredondado' 
       , convert(decimal(12, 2), round(@vl, 2, 1)) as 'truncado' 
       , convert(decimal(12, 2), @vl) as 'convertido' 

Coloquei também uma terceira coluna, onde somente converto para decimal(12,2), porém o SQL por padrão arredonda. A forma mais eficiênte é através do comando Round().

That’s all folks!

Importar um arquivo texto direto para o SQL Server

2011-08-14T01:55:00.001-03:00

Imagine que você tem um arquivo texto, delimitado por ponto-e-vírgula (um CSV) e você deseja inserí-lo diretamente dentro de uma tabela. Porém, eu não vou mostrar como fazer isso no Management Studio, que é fácil. Eu vou mostrar como fazer isso via T-SQL! E porque? Bom, para mim isto foi muito útil para automatizar uma rotina de comunicação entre sistemas, rodando automaticamente.

Vamos então para a parte prática. É muito simples, primeiro analise o arquivo texto a ser importado, e identifique todas as colunas do arquivo com seu respectivo tipo de dado (data, texto, número, money, decimal, etc). É muito importante este trabalho de análise, pois será necessário criar uma tabela exatamente com as mesmas especificações.

Vamos ver um exemplo, lançamentos contábeis. Eu tenho aqui um arquivo texto, delimitado por vírgulas gerado a partir de um sistema ERP tosco em que a contabilidade não funciona, e nós precisávamos importar para um que funcionasse, de fato, porém em SQL Server. Este tipo de coisa sempre surge no meu dia a dia, a quantidade de sistemas e a necessidade de comunicação entre eles tem crescido exponencialmente.

Uma sugestão é que sempre (quando possível) abra o arquivo no excel, estude a estrutura das colunas e seus valores, veja se não existem coisas que podem impactar na sua importação (como por exemplo aspas simples ‘ , arquivo delimitado por ponto e vírgula e o infeliz usar ponto-e-vírgula na observação do lançamento e o sistema não tratar isso) entre outras coisas… o excel ajuda muito, nomeie as colunas, insira observações, planeje, para depois partir para parte prática.

*desculpem pela formatação do blog, estou revendo este layout para permitir inserir grandes imagens…

Vamos olhar o arquivo:

Então temos algum punhado de colunas, algumas DATETIME compatível com SQL Server, outros valores texto e money, e assim por diante. Para que possamos executar esta tarefa diretamente via T-SQL, é necessário criar a tabela com precisão, para que não ocorram erros de importação, que podem comprometer a qualidade da ferramenta desenvolvida. Para o exemplo acima, eu criei a seguinte tabela:

 CREATE TABLE [dbo].[ctb_movimento_202011](
    [Coluna 0]  bigint NULL,
    [Coluna 1]  bigint NULL,
    [Coluna 2]  bigint NULL,
    [Coluna 3]  datetime NULL,
    [Coluna 4]  varchar(10) NULL,
    [Coluna 5]  varchar(500) NULL,
    [Coluna 6]  varchar(1) NULL,
    [Coluna 7]  money NULL,
    [Coluna 8]  bigint NULL,
    [Coluna 9]  varchar(50) NULL,
    [Coluna 10] bigint NULL,
    [Coluna 11] varchar(1) NULL,
    [Coluna 12] bigint NULL,
    [Coluna 13] bigint NULL,
    [Coluna 14] bigint NULL,
    [Coluna 15] bigint NULL,
    [Coluna 16] bigint NULL,
    [Coluna 17] bigint NULL,
    [Coluna 18] bigint NULL,
    [Coluna 19] bigint NULL,
    [Coluna 20] bigint NULL
)
 

Coisas de programador… coloquei estes nomes de coluna por que previamente havia feito o mapeamento em outra ferramenta (excel). Enfim, a parte prática. Para importar o arquivo, basta utilizar uma ferramenta muito útil, chamada BULK INSERT (inclusive, muito mais ampla do que este simples exemplo). Bulk significa “grande volume”, e Insert é inserir. Então vamos inserir um grande volume de dados em uma tabela.

Vamos fazer um único exemplo, porém acredito que seja o mais comum deste tipo de aplicação, o arquivo delimitado por um caracter de controle.

 BULK INSERT ctb_movimento_202011 
    FROM 'C:\Users\nms.cores\Desktop\districomp-20052011\Cordilheira\CTBMOV_D.txt' 
    WITH 
    ( 
        FIELDTERMINATOR = ';', 
        ROWTERMINATOR = '\n' 
    )
 

Este comando informa o nome da tabela que deverá ser inserida a informação (no caso, ctb_movimento_202011), a cláusula from (origem dos dados), onde especificamos a localização do arquivo texto, e logo em seguida alguns parâmetros para esta importação (dentro do WITH), como o FIELDTERMINATOR, que especifica qual é o delimitador para cada coluna (no caso, ponto e vírgula), e também qual é o delimitador de cada linha (\n no caso separado por salto de linha, veja no Books Online as opções disponíveis).

Voilà

Espero que seja útil!

Automação de impressoras térmicas diretamente pelo SQL Server

2011-06-26T22:10:00.001-03:00

*Este artigo se aplica a versões 2005 ou superior do SQL Server. Na versão 2000, haverá restrições de 8000 caracteres na geração de arquivos, devido a limitação do varchar(max) do SQL 2000.

O título pode soar um tanto estranho. Mas o fato é que esta se revelou uma grande solução “surpresa” durante o desenvolvimento de um projeto de automação através de coletores de dados.

Para explicar melhor o contexto onde isto foi adotado, tudo começou durante a implantação de um projeto de automação na conferência de mercadorias, confecção de embalagens e expedição de volumes. Adotamos 3 coletores ARGOX PT-60 (fig.1) com Windows CE 5.0 e conexão wireless e 3 impressoras ZEBRA GK420t (fig.2) instaladas em um único computador dedicado, compartilhando as impressoras pela rede.


Fig.1 Coletor Argox PT-60	Fig.2 Impressora Térmica ZEBRA GK420t

O sistema, nomeado ARKAB, foi desenvolvido em vb.net utilizando o MS Compact framework. Basicamente, o sistema atuava de forma on-line com o servidor SQL, buscando dados referente os pedidos de venda, e alimentando tabelas durante a conferência, possibilitando que dois operadores pudessem conferir o mesmo pedido.

Este sistema permite conferir as mercadorias que estão no pedido de venda. O separador pega todos os produtos utilizando o romaneio de separação (fig.3), e entrega em um carrinho para o conferente. Este identifica o pedido no coletor através de código de barras (fig.3), identifica-se através do seu crachá pessoal e também identifica qual impressora irá utilizar (código de barras na impressora, conf. fig.4).


Fig.3 Romaneio de Separação	Fig.4 Identificação da impressora

Mas aí surgiram problemas, que prejudicaram o funcionamento do projeto. Durante o final do mês, a empresa realiza muito processamento no servidor na geração de lotes contábeis, fechamentos fiscais, emissão de DREs pesadas e abarrotadas de dados. E os coletores começaram a ficar muito lentos, prejudicando significativamente a performance da equipe de separação.

Outro problema que ocorria sempre era ao enviar uma impressão pela rede. De alguma forma, o Windows CE exibe uma messagebox com o resultado do comando de impressão ao usuário, o que atrapalha (e muito!) durante a conferência. A mensagem aparece na tela, o usuário não percebe, e ele bipa um produto. Como no final do comando sempre tem um ENTER, a tela de mensagem é fechada, e o usuário nem teria percebido que aquele produto bipado somente fechou a mensagem, e não foi processado pelo software. Isto acontecia por que eu gerava um arquivo texto temporário no coletor, e o copiava pela rede diretamente para a impressora, e o próprio Windows CE gerencia esta mensagem, o que parece não ser possível desativar.

Para resolver isto, modifiquei o sistema do coletor, fazendo com que carregasse em DATASET todos os dados necessários para realizar a conferência, de forma que acessasse o banco de dados duas vezes (na abertura e no fechamento da conferência), e não mais em cada operação. O aumento de performance foi absoluto, o coletor não dependia mais da rede para ter um bom desempenho ou não. Mas ainda restava a etiqueta. Como fazer a impressão de forma que não aparecesse esta mensagem de status de impressão ao usuário?

Resolvi então fazer a impressão da etiqueta térmica utliizando o próprio SQL Server, de forma que o servidor enviasse o comando para a impressora, e não mais o coletor. A solução aparentemente muito simples e funcional me fez perceber que podemos fazer qualquer sistema que utilize base SQL Server imprimir diretamente em impressoras térmicas de linguagem específica (PPLA, PPLB, ZPL, etc).

Para isso, vamos dividir esta informação em três partes fundamentais: geração dos dados da etiqueta, salvar estes dados em um arquivo texto e finalmente copiar este arquivo diretamente à impressora desejada pela rede.

1. Geração dos dados da etiqueta

O primeiro procedimento que montei é o que realiza a busca de todas as informações no banco de dados para a impressão da etiqueta, e monta dinamicamente todos os comandos ZPL necessários para a impressão da etiqueta:

Code Snippet
 -- set nocount on
-- exec spArkabEtiqueta 9
create proc  [dbo].[spArkabEtiqueta]
  @id_volume int
, @cd_impressora int
as
 
  /*
  Autor: Fabiano Cores
  Data : 06/2011
  Objetivo: Gera a etiqueta através de comandos ZPL, salva em arquivo
  através de automação OLE e lança o arquivo diretamente para a impressora
  térmica na rede através de simples compartilhamento, somente copiando
  o arquivo para o dispositivo (como \\pc-na-rede\nome_imp_zebra)
  */
 
  declare @t table (texto varchar(8000) null, ordem int not null identity)
  
  declare @cd_empresa     int
  declare @nr_pedido      int
  declare @nr_volume      int
  declare @qt_volume      int
  declare @cd_usuario     int
  declare @qt_pecas       int
  declare @cliente        varchar(60)
  declare @transportadora varchar(60)
  declare @endereco       varchar(200)
  declare @complemento    varchar(200)
  declare @pedido         varchar(20)
  declare @volume         varchar(20)
  declare @responsavel    varchar(50)
  declare @data           varchar(20)
  declare @quantidade     varchar(20)
  declare @cdb            varchar(50)
  declare @vtexto         varchar(8000)
  declare @texto          varchar(max)
  declare @diretorio      varchar(255)
  declare @arquivo        varchar(100)
  declare @origem         varchar(500)
  declare @destino        varchar(500)
  
  select @cd_empresa = cd_empresa
       , @nr_pedido  = nr_pedido
       , @nr_volume  = nr_volume
       , @cd_usuario = cd_usuario
       , @cdb        = cdb
    from ArkabVolume
   where id_volume   = @id_volume
   
  select @qt_pecas = sum(qt_item)
    from arkabvolumeitem
   where id_Volume = @id_volume
  
  select @qt_volume = count(*)
    from ArkabVolume
   where cd_empresa = @cd_empresa
     and nr_pedido  = @nr_pedido
   
  select @cliente = rtrim(cadastro.razao)
       , @transportadora = isnull(rtrim(transp.razao),'')
       , @endereco = rtrim(cadastro.endereco)
       , @complemento = rtrim(ltrim( rtrim(isnull(cadastro.complemento, '')) 
           + ' ' + rtrim(cidade.nome) + '/' + rtrim(cidade.cd_estado) 
           + ' - ' + left(rtrim(cadastro.cep),5) + '-' + right(rtrim(cadastro.cep),3)))
       , @pedido = right('000000' + rtrim(@nr_pedido), 6)
       , @volume = right('000' + rtrim(@nr_volume),3)--  + '/' + right('00' + rtrim(@qt_volume),2)
       , @responsavel = rtrim(usuario.login)
       , @data = convert(char(10), getdate(), 103) + ' ' 
         + right('00' + rtrim(datepart(hour, getdate())),2) + 'h'
       , @quantidade = rtrim(@qt_pecas) + ' PC'
       
    from cadastro, cidade, usuario, pedidovenda, cadastro transp
   where pedidovenda.cd_cadastro = cadastro.cd_cadastro
     and cidade.cd_Cidade = cadastro.cd_cidade
     and pedidovenda.cd_empresa = @cd_empresa
     and pedidovenda.nr_pedido  = @nr_pedido
     and usuario.cd_usuario = @cd_usuario
     and pedidovenda.cd_transportador *= transp.cd_cadastro
     
  select @texto = '
^XA
^MD10
^LT0
^JMA^FS
^MUd,200,200
^LH6,0
^FO20,7
^GB780,625,5,,0
^FS
^FO20,70
^GB645,120,5,,0
^FS
^FO20,7
^GB780,70,70,,0
^FS
^FO660,70
^GB140,562,5,,0
^FS
^FO20,275
^GB235,160,5,,0
^FS
^FO30,305
^GB215,120,120,,0
^FS
^FO250,275
^GB180,160,5,,0
^FS
^FO260,305
^GB160,120,120,,0
^FS
^FO425,275
^GB240,85,5,,0
^FS
^FO425,355
^GB240,80,5,,0
^FS
^FO425,430
^GB240,75,5,,0
^FS
^FO425,500
^GB240,132,5,,0
^FS
^FO90,20,0
^ATN,30,20
^FR
^FD
DECORLUX MATERIAL ELETRICO LTDA
^FS
^FO30,80,0
^AQN,20,10
^FD
CLIENTE
^FS
^FO30,103,0
^ATN,30,20
^FD
' + left(rtrim(@cliente), 29) + '
^FS
^FO30,138,0
^ATN,30,20
^FD
' + substring(@cliente, 30, 29) + '
^FS
^FO30,138,0
^ASN,30,20
^FD
^FS
^FO30,200,0
^AQN,20,10
^FD
TRANSPORTADORA
^FS
^FO30,223,0
^ASN,30,20
^FD
' + rtrim(@transportadora) + '
^FS
^FO30,280,0
^AQN,20,10
^FD
PEDIDO DE VENDA
^FS
^FO40,290,0
^AVN,120,100
^FR
^FD
' + rtrim(@pedido) + '
^FS
^FO265,280,0
^AQN,20,10
^FD
VOLUME
^FS
^FO290,290,0
^AVN,120,100
^FR
^FD
' + rtrim(@volume) + '
^FS
^FO435,280,0
^AQN,20,10
^FD
RESPONSAVEL
^FS
^FO435,310,0
^ASN,20,10
^FD
' + rtrim(@responsavel) + '
^FS
 
^FO435,360,0
^AQN,20,10
^FD
DATA
^FS
 
^FO435,385,0
^ASN,30,20
^FD
' + rtrim(@data) + '
^FS
 
^FO435,435,0
^AQN,20,10
^FD
QUANTIDADE
^FS
 
^FO435,460,0
^ASN,30,20
^FD
' + rtrim(@quantidade) + '
^FS
 
^FO30,435,0
^AQN,20,10
^FD
NOTA FISCAL E VISTO
^FS
 
^FO690,97
^BY2
^BCR,100,Y,N,N
^FD
' + rtrim(@cdb) + '
^FS
^XZ'
 
  -- Diretorio para gravar o arquivo temporário
  set @diretorio = 'D:\Arkab\impressoes\'
  
  -- Definição do nome do arquivo, coloca dia, mes, ano,
  -- hora, minuto, segundo e ID do volume.
  select @arquivo = 'tmp' 
                  + right('00' + rtrim(datepart(day, getdate())),2)
                  + right('00' + rtrim(datepart(month, getdate())),2)
                  + rtrim(datepart(year, getdate()))
                  + right('00' + rtrim(datepart(hour, getdate())),2)
                  + right('00' + rtrim(datepart(minute, getdate())),2)
                  + right('00' + rtrim(datepart(second, getdate())),2)
                  + '_vol_' + rtrim(@id_volume) + '.txt'
                  
  -- Gravo o arquivo
  exec spArkabGravaArquivo
    @texto, @diretorio, @arquivo
    
  -- Definição da origem e destino do arquivo salvo.
  select @origem = rtrim(@diretorio) + rtrim(@arquivo)
 
  -- Algo como '\\PC-CONF01\ZEBRA-SEP1'
  select @destino = caminho_de_rede
    from ArkabImpressora
   where cd_impressora = @cd_impressora
  
  -- Envio o arquivo texto pela rede diretamente na impressora térmica.
  exec spArkabCopiaArquivo
    @origem, @destino
 

Este procedimento coleta todos os dados, concatena em um varchar(max), e passa esta string para a segunda etapa, que é gravar este texto em um arquivo em uma pasta específica no servidor.

Exemplo do texto gerado pelo procedimento:

^XA
^MD10
^LT0
^JMA^FS
^MUd,200,200
^LH6,0
^FO20,7
^GB780,625,5,,0
^FS
^FO20,70
^GB645,120,5,,0
^FS
^FO20,7
^GB780,70,70,,0
^FS
^FO660,70
^GB140,562,5,,0
^FS
^FO20,275
^GB235,160,5,,0
^FS
^FO30,305
^GB215,120,120,,0
^FS
^FO250,275
^GB180,160,5,,0
^FS
^FO260,305
^GB160,120,120,,0
^FS
^FO425,275
^GB240,85,5,,0
^FS
^FO425,355
^GB240,80,5,,0
^FS
^FO425,430
^GB240,75,5,,0
^FS
^FO425,500
^GB240,132,5,,0
^FS
^FO90,20,0
^ATN,30,20
^FR
^FD
DECORLUX MATERIAL ELETRICO LTDA
^FS
^FO30,80,0
^AQN,20,10
^FD
CLIENTE
^FS
^FO30,103,0
^ATN,30,20
^FD
TORNADO INDUSTRIA E PECAS LTD
^FS
^FO30,138,0
^ATN,30,20
^FD
A
^FS
^FO30,138,0
^ASN,30,20
^FD
^FS
^FO30,200,0
^AQN,20,10
^FD
TRANSPORTADORA
^FS
^FO30,223,0
^ASN,30,20
^FD
NATALIN DA SILVA RAMOS
^FS
^FO30,280,0
^AQN,20,10
^FD
PEDIDO DE VENDA
^FS
^FO40,290,0
^AVN,120,100
^FR
^FD
056783
^FS
^FO265,280,0
^AQN,20,10
^FD
VOLUME
^FS
^FO290,290,0
^AVN,120,100
^FR
^FD
009
^FS
^FO435,280,0
^AQN,20,10
^FD
RESPONSAVEL
^FS
^FO435,310,0
^ASN,20,10
^FD
AUTOMATICO
^FS

^FO435,360,0
^AQN,20,10
^FD
DATA
^FS

^FO435,385,0
^ASN,30,20
^FD
26/06/2011 21h
^FS

^FO435,435,0
^AQN,20,10
^FD
QUANTIDADE
^FS

^FO435,460,0
^ASN,30,20
^FD
1 PC
^FS

^FO30,435,0
^AQN,20,10
^FD
NOTA FISCAL E VISTO
^FS

^FO690,97
^BY2
^BCR,100,Y,N,N
^FD
00020567830099699335
^FS
^XZ

Veja que depois defino qual o destino final do arquivo (no caso, uma impressora zebra na rede). No caso do sistema Arkab, a definição da impressora é dinâmica, no momento da abertura da conferência o usuário seleciona qual irá utilizar. Ao imprimir a etiqueta, o sistema passa para o procedimento como parâmetro (@cd_impressora) qual será a impressora que deverá enviar o comando. Uma simples tabela permite referenciar o código desta impressora ao seu caminho de rede.

E logo no final, temos o procedimento spArkabCopiaArquivo, que faz a cópia do arquivo do servidor para a impressora utilizando a rede. Vamos então falar de cada um deles agora.

2. spArkabGravaArquivo - Este procedimento grava o texto desejado em um arquivo em disco:

Code Snippet
 create proc spArkabGravaArquivo
  @texto varchar(max), --8000 in SQL Server 2000
  @diretorio varchar(255),
  @arquivo varchar(100)
 
as 
 
  /*
  Autor: Fabiano Cores
  Data : 06/2011
  Objetivo: Permite gravar arquivos de texto em disco diretamente
  através do T-SQL.
 
  Observação importante: pode haver a necessidade de ativar a
  opção 'Ole Automation Procedures' no SQL Server através do comando
  sp_reconfigure. Para isto, utilize o script abaixo:
  
  -- Mostrar opções avançadas.
  exec sp_configure 'show advanced options', 1
  GO
  -- Ativar a opção anterior
  reconfigure
  GO
  -- Ativa a automação OLE
  exec sp_configure 'Ole Automation Procedures', 1
  GO
  -- Reconfigura para aceitar a modificação
  reconfigure
  GO
  */
 
  declare @objFileSystem   int
  declare @objTextStream   int
  declare @hr              int
  declare @fileAndPath     varchar(80)
 
  exec @hr = sp_OACreate 'Scripting.FileSystemObject'
                       , @objFileSystem output
 
  select @FileAndPath = @diretorio + '\' + @arquivo
  
  if @hr = 0 
    exec @hr = sp_OAMethod @objFileSystem
                         , 'CreateTextFile'
                         , @objTextStream output
                         , @FileAndPath
                         , 2
                         , True
 
  if @hr=0
    exec @hr = sp_OAMethod @objTextStream
                         , 'Write'
                         , Null
                         , @texto
 
  if @hr = 0
    exec @hr = sp_OAMethod @objTextStream, 'Close'
 
  exec sp_OADestroy @objTextStream
  exec sp_OADestroy @objTextStream
 

3. spArkabCopiaArquivo – Realiza a cópia do arquivo da etiqueta diretamente para a impressora:

 /*
-- Para testar, copia o arquivo direto para impressora SEP1.
exec spArkabCopiaArquivo 
    'D:\Arkab\Impressoes\tmp26062011030828_vol_4.txt'
  , '\\PC-CONF01\ZEBRA-SEP1'
*/
create proc spArkabCopiaArquivo
  @origem varchar(200)
, @destino varchar(200)
 
as
 
  /*
  Autor: Fabiano Cores
  Data : 06/2011
  Objetivo: Permite copiar arquivos, informando origem e destino.
 
  Observação importante: pode haver a necessidade de ativar a
  opção 'xp_cmdshell' no SQL Server através do comando
  sp_reconfigure. Para isto, utilize o script abaixo:
  
  -- Mostrar opções avançadas.
  exec sp_configure 'show advanced options', 1
  GO
  -- Ativar a opção anterior
  reconfigure
  GO
  -- Ativa o comando
  exec sp_configure 'xp_cmdshell', 1
  GO
  -- Reconfigura para aceitar a modificação
  reconfigure
  GO
  */
 
  declare @string varchar(400)
  
  select @string = 'copy ' + rtrim(@origem) + ' ' + rtrim(@destino)
 
  exec master.dbo.xp_cmdshell @string, no_output
 

O resultado, é uma etiqueta saindo pela impressora, como esta daqui de exemplo (exatamente a mesma do procedimento acima):

Uma solução interessante…

SPLIT STRING – Função para dividir uma string por um caracter de controle

2011-06-17T03:04:00.001-03:00

Imagine a seguinte situação: você possui uma string, separada por ponto e vírgula, e você deseja separar estas strings para realizar um tratamento. Vamos ver um exemplo abaixo:

string1; string2; "string 3 com ; no meio"; string4

Então nós temos quatro valores concatenados em uma única string. Observe que o terceiro valor utiliza o ponto e vírgula como texto da própria string, porém a sentença toda está entre aspas (“), o que determina que o ponto e virgula desta frase faz parte da string, e não é um caracter separador.

Para resolver esta questão, eu desenvolvi o seguinte procedimento:

 -- exec spSplit 'string1; string2; "string3 com ; no meio"; string4', ';'
alter proc spSplit
  @grupo varchar(8000),
  @caracter char(1) = NULL
as
 
begin
 
  -- ***************
  -- SQL Burger 2011
  -- Função SPLIT, separa string dividida por
  -- caracter de controle. Esta função só pode ser
  -- utilizada e distribuída caso os créditos ao autor
  -- sejam citados.
  -- ***************
  
  if @caracter is null set @caracter = ','
 
  declare @pos int
  declare @buffer varchar(8000)
  declare @buffer2 varchar(8000)
 
  create table #ret (texto varchar(8000) null, ordem int not null identity(1,1))
 
  while charindex('"', @grupo) > 0
  begin
 
    select @pos = charindex('"', @grupo)
 
    select @buffer = left(@grupo, @pos)
 
    select @grupo = substring(@grupo, @pos + 1, len(@grupo))
 
    select @pos = charindex('"', @grupo)
 
    select @buffer2 = left(@grupo, @pos)
 
    select @grupo = substring(@grupo, @pos + 1, len(@grupo))
 
    select @buffer = replace(@buffer, '"','')
         , @buffer2 = replace(@buffer2, '"', '')
 
    select @buffer2 = replace(@buffer2, @caracter, char(254))
    
    select @buffer2 = replace(@buffer2, ';', char(253))
 
    select @grupo = rtrim(@buffer) + rtrim(@buffer2) + rtrim(@grupo)
 
  end
  
  while charindex(@caracter,@grupo) > 0
  begin
    select @pos = charindex(@caracter,@grupo)
    select @buffer = left(@grupo,@pos - 1)
    select @grupo = right(@grupo,len(rtrim(@grupo)) - @pos)
    
    select @buffer = replace(@buffer, char(254), @caracter)
    select @buffer = replace(@buffer, char(253), ';')
    
    insert into #ret (texto)
      select rtrim(ltrim(@buffer))
  end
 
  if @grupo <> ''
  begin
    select @buffer =replace(@grupo,@caracter,'')
 
    insert into #ret (texto)
      select rtrim(ltrim(@buffer))
  end
 
  select texto from #ret order by ordem
 
end
 

O procedimento recebe dois parâmetros: @grupo (que é string a ser tratada) e @caracter (o caracter de controle a ser utilizado para separar a string). O resultado é o seguinte:

 exec spSplit 'string1; string2; "string3 com ; no meio"; string4', '!'
 

A procedure irá retornar cada string em uma linha, permitindo tratá-las da maneira como quiser. Uma dica, utilize uma tabela temporária com uma coluna auto-numérica, e você saberá exatamente qual linha ler de acordo com a posição desejada:

 set nocount on
 
create table #split (
  texto varchar(100) null
, ordem int not null identity )
 
insert into #split (texto)
  exec spSplit 'string1; string2; "string3 com ; no meio"; string4', ';'
  
select texto as 'Primeiro parametro' from #split where ordem = 1
select texto as 'Segundo parametro' from #split where ordem = 2
select texto as 'Terceiro parametro' from #split where ordem = 3
select texto as 'Quarto parametro' from #split where ordem = 4
 
 
drop table #split
 

Desta forma, o número da linha corresponderá ao parâmetro:

Outros caracteres de controle poderão ser utilizados, trazendo o mesmo resultado:

 exec spSplit 'string1! string2! "string3 com ; no meio"! string4', '!'
 

 exec spSplit 'string1! string2! "string3 com ! no meio"! string4', '!'
 

Espero que seja útil!

row_number : Use o número da linha em suas consultas

2011-05-25T17:51:00.001-03:00

Durante o desenvolvimento de um layout de exportação de dados por arquivo texto, utilizei uma ferramenta muito prática do Transact-SQL, que gostaria de compartilhar aqui no meu blog. A função ROW_NUMBER é utilizada para retornar o número da linha dentro da consulta, de acordo com o “partition_by” e o “order_by” informados. Vamos ver a estrutura da função:

 ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> )
 

[ <partition_by_clause> ]

Este parâmetro não é obrigatório. Permite especificar o particionamento da numeração da linha. É um agrupamento, que permitirá que a numeração seja sempre iniciada a cada mudança do agrupamento / particionamento.

<order_by_clause>

Determina qual será a ordenação das linhas do select na atribuição da numeração. A ordenação da função row_number não precisa necessariamente ser a mesma ordenação do select principal onde a função ROW_NUMBER foi aplicada.

A função irá retornar um BIGINT com o respectivo número da linha. Vamos então para a parte prática!

Crie a seguinte tabela e execute uma carga de exemplo:

 create table exemplo_rn (
  funcao char(15) null
, nome char(15) null
)
 
insert into exemplo_rn ( funcao, nome ) values ( 'PROGRAMADOR' , 'JOAO' )
insert into exemplo_rn ( funcao, nome ) values ( 'PROGRAMADOR' , 'MARIA' )
insert into exemplo_rn ( funcao, nome ) values ( 'PROGRAMADOR' , 'MARCOS' )
insert into exemplo_rn ( funcao, nome ) values ( 'PROGRAMADOR' , 'VERIDIANA' )
 
insert into exemplo_rn ( funcao, nome ) values ( 'ANALISTA' , 'IGOR' )
insert into exemplo_rn ( funcao, nome ) values ( 'ANALISTA' , 'PEDRO' )
 
insert into exemplo_rn ( funcao, nome ) values ( 'COORDENADOR' , 'OSVALDO' )
 

Nós vamos utilizar como exemplo uma pequena tabela, que possui função e nome de colaboradores. Claro, reduzimos apenas às colunas que nos interessa para exemplificarmos.

Vamos então consultar nossa tabela de exemplo:

 select * from exemplo_rn
 

Então nós temos 7 linhas no total. Vamos agora retornar uma terceira coluna, que trará automaticamente o número da linha:

 select funcao, nome, row_number() over ( order by nome ) as 'ID'
  from exemplo_rn
 order by nome
 

A coluna ID é resultado da função row_number(). Neste primeiro exemplo, utilizamos a ordenação alfabética da coluna “nome” como condição de numeração das linhas. Observe que a ordenação dentro da cláusula OVER () e a ordenação do select principal não estão diretamente interligadas, porém a ordenação dentro de OVER() é obrigatória:

 select funcao, nome, row_number() over ( order by funcao, nome ) as 'ID'
  from exemplo_rn
 order by funcao, nome
 

Neste segundo exemplo, a numeração foi feita através da ordenação da função e nome, na respectiva ordem. O próximo exemplo mostra a utilização de PARTITION_BY, condicionando a numeração das linhas ao agrupamento de respectivos campos:

 select funcao
     , nome
     , row_number() over ( partition by funcao order by nome ) as 'ID'
  from exemplo_rn
 order by funcao
 

Observe no resultado que a numeração da linha foi feita para cada agrupamento utilizado.

Crystal Reports – Como buscar uma imagem de arquivo para ser exibida no relatório

2011-03-17T01:45:00.001-03:00

Olá caros sqlnautas, gostaria de retornar aqui com mais uma dica importante sobre imagens no crystal reports. Imagine o seguinte cenário: você está montando um relatório em crystal reports que deverá exibir imagens que, ao invés de estarem armazenadas no banco de dados, estão em arquivos no disco rígido do servidor, e no banco de dados você só tem a localização deste arquivo, o que é muito comum, pois armazenar arquivos em banco de dados é extremamente desaconselhável quando se pensa em performance.

O que precisamos fazer, é utilizar uma ferramenta muito simples, chamada BULK INSERT. Este comando é utilizado para importar dados em massa provenientes de arquivos para o banco de dados. Mas nós não vamos armazenar esta imagens, e sim converter os arquivos de imagem em formato BLOB, ou image. O conceito é simples: realizo o bulk insert do arquivo dentro de uma tabela temporária, que retorna um select ao crystal reports com a coluna image da tabela:

Code Snippet
 -- exec spcAmplaPedidoCompraImagem 3, 2685
alter proc spcAmplaPedidoCompraImagem  
  @cd_empresa int
, @nr_pedido int
 
as
 
  declare @nr_documento int
  declare @revisao int
  declare @vcd_item char(13)
  declare @vdocumento varchar(4000)
  declare @sql varchar(8000)
  
  -- ***************
  -- SQL Burger 2011
  -- Procedure que retorna as imagens provenientes de arquivos
  -- para exibição em relatório crystal reports.
  -- ***************
  
  -- Inicio a montagem de uma query dinâmica, necessária para a execução
  -- do comando bulkinsert com parâmetros dinâmicos, provenientes da tabela
  -- que possui a localização dos arquivos.
  select @sql = '
 
  create table #tmpImagem (imagem image)
  
  '
  
  -- Insiro em uma tabela auxiliar todos os itens do pedido de compra
  -- para verificar quais possuem documentos que deverão ser impressos
  -- junto com o pedido.
  create table #tmpItem (cd_item char(13) collate database_Default null)
  
  insert into #tmpItem
    select cd_item from pedidocitem
     where cd_empresa = @cd_empresa
       and nr_pedido = @nr_pedido
       
  insert into #tmpItem
    select cd_item from AdhEPTRomaneioPedidoCompra
     where cd_empresa = @cd_empresa
       and nr_pedido = @nr_pedido
       
  -- Faço join da tabela temporária de itens com a de documentos
  -- configurados a serem impressos nos pedidos de compra, e valido
  -- se o arquivo está ativo e dentro da validade.
  declare cp1 cursor local fast_forward read_only for
    select distinct a.cd_item, b.documento
      from #tmpItem a, itemdocumentorev b
     where a.cd_item = b.cd_item
       and b.imprime_pc = 'S'
       and b.cd_situacao = 'A' -- ativos apenas
       and b.dt_validade >= convert(char(10), getdate(), 120)
     order by a.cd_Item
       
  open cp1
  
  while 1 = 1
  begin
    fetch next from cp1 into @vcd_item, @vdocumento
    if @@fetch_status <> 0 break
    
    -- Para cada arquivo retornado, monto na query dinâmica o comando bulk insert
    -- que irá inserir o stream da imagem na tabela #tmpImagem, criada no começo
    -- do procedimento.
    select @sql = @sql + '  insert into #tmpImagem (imagem)
      select Bulkcolumn 
        from OPENROWSET(
               BULK N''' + rtrim(@vdocumento) + ''', SINGLE_BLOB
                       ) as picture      
    '
  
  end
  close cp1
  deallocate cp1
  
  -- No final, retorno as imagens.
  select @sql = @sql + '
  
    select imagem from #tmpImagem
    
  '
  
  -- Executo a query dinâmica
  exec (@sql)
 

A string que contem a query dinâmica (@sql) foi executada da seguinte forma:

Code Snippet
 create table #tmpImagem (imagem image)
 
  insert into #tmpImagem (imagem)
    select Bulkcolumn 
      from OPENROWSET(
             BULK N'C:\MSSQL\Novo\4002-109 Base Fixação Encoder0001.jpg', SINGLE_BLOB
                     ) as picture      
    insert into #tmpImagem (imagem)
    select Bulkcolumn 
      from OPENROWSET(
             BULK N'C:\MSSQL\Novo\4004-057 Suporte Sensor Ótico0001.jpg', SINGLE_BLOB
                     ) as picture      
    insert into #tmpImagem (imagem)
    select Bulkcolumn 
      from OPENROWSET(
             BULK N'C:\MSSQL\Novo\4004-061 Suporte Direito Bandeja0001.jpg', SINGLE_BLOB
                     ) as picture      
    insert into #tmpImagem (imagem)
    select Bulkcolumn 
      from OPENROWSET(
             BULK N'C:\MSSQL\Novo\4004-064 Suporte Esquerdo Bandeja0001.jpg', SINGLE_BLOB
                     ) as picture      
    insert into #tmpImagem (imagem)
    select Bulkcolumn 
      from OPENROWSET(
             BULK N'C:\MSSQL\Novo\4004-119B Placa Fixação Servo0001.jpg', SINGLE_BLOB
                     ) as picture      
  
 
  select imagem from #tmpImagem
 

Para cada arquivo, ele montou a string executando o bulk insert de cada imagem. No final, o select retornando as imagens da temporária. O resultado do procedimento, via query no SQL Server:

Já no relatório crystal reports, ao importar esta coluna do tipo IMAGE, aparecerá no seu relatório:

Easy, don’t you think ? =)

Até a próxima!!

Um caso de recursividade

2010-12-23T01:22:00.001-02:00

Aproveitando um recente trabalho realizado, gostaria de descrever um relatório que rapidamente desenvolvi em uma base de dados SQL Server. A situação era a seguinte: eu havia importado a estrutura de um produto, e precisava exibir esta estrutura na forma de um relatório. Primeiro, vamos entender o termo “estrutura de um produto”. A empresa em questão é uma fábrica de impressoras de plotagem (1,8m a 3,2m). A ficha técnica da impressora é composta por vários níveis de componentes. Vou ilustrar uma composição:

Impressora XPTO modelo 1

Conjunto eletrônica XPTO 1804

Tomada de embutir 3 pinos
Cinta nylon média
Fusível pequeno 10A
Sensor de temperatura
Ventilador – secagem

Espaguete termo retrátil
Ventilador 120x120x38 c/ rosca
Conector modular miniatura H89F09

Blower – vácuo

Blower ( Siroco ) AD xxxx 24v
Conector modular miniatura H90F23

Aquecimento – cabeças

Resistor Fio 120R x 10W
Cabo paralelo 0,20mm
Apoio de silicone 13mm x 4mm
Terminal KK pequeno importado

O relatório precisa aparecer formatado como está acima (indentado nos níveis). Porém, a quantidade de níveis é indefinida. Desta forma, utilizei o conceito de “recursividade”.

Definições de recursividade na internet:

Recursão é um método de programação no qual uma função pode chamar a si mesma. O termo é usado de maneira mais geral para descrever o processo de repetição de um objeto de um jeito similar ao que já fora mostrado. ...
pt.wikipedia.org/wiki/Recursividade
A recursividade na programação de computadores envolve a definição de uma função que pode invocar a si própria. Um exemplo de aplicação da recursividade pode ser encontrado nos analisadores gramaticais recursivos para linguagens de programação. ...
pt.wikipedia.org/wiki/Recursividade_(ciência_da_computação)
iteratividade; propriedade de formar infinitos elementos à partir de finitos aplicando-se regras lógicas ou matemáticas
pt.wiktionary.org/wiki/Recursividade
que chama a si mesmo (programa ou função)
pt.wiktionary.org/wiki/Recursivo

Para desenvolver este relatório, eu utilizei este conceito de recursividade. Eu criei um procedimento insere os componentes, e para cada componente que está inserindo, chama a si mesmo para repetir a explosão, independente da quantidade de níveis que houver na estrutura.

O primeiro código que vamos analisar é o que retorna o relatório ao usuário. Ele somente recebe os parâmetros, e chama o procedimento de explosão da estrutura, chamado spcAdhExpEst (procedimento recursivo).

Code Snippet
  
CREATE proc spcAdhExplodeProduto
  @cd_item char(13)
  
as
 
  /*
   Autor: Fabiano Cores
   Data : 23/12/2010
   Site : http://sqlburger.blogspot.com
   Desenvolvido para demonstrar a utilização de procedimentos
   recursivos no SQL Server. A reprodução deste artigo só é 
   permitida se forem mantidos os créditos do autor.
   */
 
  -- Esta tabela possui papel fundamental na recursividade. Esta
  -- tabela irá existir durante toda a execução deste procedimento
  -- mesmo quando chamamos executamos um segundo procedimento. 
  create table #retorno (
    cd_componente char(18) collate database_default null
  , qt_aplicada decimal(10,4) null
  , nivel smallint null
  , ordem int not null identity)
  
  -- Insiro o item pai, para que o usuário saiba de qual produto
  -- está tirando o relatório
  insert into #retorno (cd_componente, qt_aplicada, nivel)
    select @cd_item, 1, 1
  
  -- Este é o procedimento recursivo. Ele fará referencia a ele
  -- mesmo, enquanto preenche a tabela #retorno
  exec spcAdhExpEst @cd_item
 
  -- Aqui, eu pego os registros da tabela #retorno, e retorno
  -- ao software .NET, para exibição ao usuário.
  select convert(char(18), space((nivel-1) * 2) + a.cd_Componente) as 'Código'
       , convert(char(65), space((nivel-1) * 2) + b.descricao) as 'Descrição'
       , a.qt_aplicada 'Quantidade'
       , a.nivel -1 as 'Nível'
    from #retorno a, item b
   where a.cd_componente = b.cd_item
   order by a.ordem
 
--
 

Este é o código do procedimento recursivo. Ele insere na tabela #retorno, que foi criada pelo seu procedimento antecessor (spcAdhExplodeEstrutura), e faz referência a si mesma:

Code Snippet
 -- 
 
CREATE proc spcAdhExpEst
  @cd_item char(13)
  
as
 
   /*
   Autor: Fabiano Cores
   Data : 23/12/2010
   Site : http://sqlburger.blogspot.com
   Desenvolvido para demonstrar a utilização de procedimentos
   recursivos no SQL Server. A reprodução deste artigo só é 
   permitida se forem mantidos os créditos do autor.
   */
 
  declare @vcd_componente char(13)
  declare @vqt_aplicada decimal(19,4)
 
  -- Insere os componentes simples (não possuem estrutura abaixo)
  insert into #retorno (cd_componente, qt_aplicada, nivel)
    select a.cd_componente, a.qt_aplicada, @@nestlevel
      from processoitem a, item b
     where a.cd_item = @cd_item
       and a.cd_componente = b.cd_item
       and not exists (select 0
                         from processoitem c
                        where c.cd_item = a.cd_componente)
     order by b.descricao
 
  -- Aqui é montado um cursor nos itens complexos (que possuem
  -- estrutura). É utilizado um cursor por que para cada item
  -- que possui estrutura, eu vou fazer a chamada recursiva.
  declare c1 cursor local fast_forward read_only for
    select a.cd_componente, a.qt_aplicada
      from processoitem a, item b
     where a.cd_item = @cd_item
       and a.cd_componente = b.cd_item
       and     exists (select 0
                         from processoitem c
                        where c.cd_item = a.cd_componente)
     order by b.descricao
     
  open c1
  
  while 1 = 1
  begin
    fetch next from c1 into @vcd_componente, @vqt_aplicada
    if @@fetch_status <> 0 break
    
    insert into #retorno (cd_componente, qt_aplicada, nivel)
      select @vcd_componente, @vqt_aplicada, @@nestlevel
      
    -- Chamada recursiva (executando ele mesmo)
    exec spcAdhExpEst @vcd_componente
    
  end
  close c1
  deallocate c1
 
--
 

Detalhes importantes que devem ser observados:

1. A tabela temporária foi criada no primeiro procedimento. Se ela fosse criada no procedimento recursivo, daria erro, pois ele tentaria criar mais de uma vez a mesma tabela, desta forma, ela sendo criada apenas uma vez pelo procedimento pai, não haverá problemas.

2. O procedimento filho (spcAdhExpEst) consegue enxergar a tabela #retorno.

3. A variável @@nestlevel é uma variável de ambiente, que retorna o nível de aninhamento* da execução do procedimento. Isto é uma forma de saber quantas vezes o procedimento já foi disparado por ele mesmo (na primeira execução, @@nestlevel é igual a 0). Aproveitei esta variável para incluir no relatório a que nível o componente pertence, e também a utilizei como parâmetro para adicionar espaços à esquerda do código e da descrição, para melhorar visualmente a identificação dos níveis na leitura do relatório.

*Definições de aninhar na internet:

aninhado - Quando se tem um objeto dentro do outro, como uma tabela, por exemplo , você possui um aninhamento , ou seja, um objeto funcionando como ninho para outro objeto.
www.juliobattisti.com.br/tutoriais/keniareis/dicionarioinfo001.asp

Eu estou postando esta dica por que hoje tive uma experiência muito desagradável… um certo programador teve a capacidade de fazer 9 cursores (loops), um dentro do outro, para tratar possíveis 9 níveis do produto. Além de ser um código absurdamente mal pensado, é gigantesco, lento, péssimo de realizar manutenção, entre outros comentários que não vêem ao caso. E como padrão, quando pego código assim, tenho o hábito de selecionar tudo, apagar, refazer… e claro, compartilhar!

Triggers para iniciantes no SQL Server – Parte 2/3

2010-12-22T20:34:00.001-02:00

Este artigo se aplica ao Microsoft SQL Server a partir da versão 2000, porém o conceito de triggers se aplica à maioria dos sistemas gerenciadores de bancos de dados disponíveis, porém podem ocorrer diferença de sintaxe.

Olá caros sqlnautas, sejam bem vindos à segunda parte da saga sobre triggers (gatilhos) no SQL Server. Para quem deseja ler a primeira parte, segue aqui o link para a primeira parte:

Triggers para iniciantes no SQL Server - Parte 1/3

PARA COMEÇAR

Para esta segunda parte do nosso tutorial, vamos partir para a parte prática. Para começar, abra uma nova janela de Query no seu SQL Management Studio para criarmos duas tabelas, com estruturas idênticas, porém com diferentes propósitos, para que possamos executar nossos testes, e entender o comportamento das triggers em todas as operações.

Code Snippet
 create table exTrigger1 ( 
  codigo     char(13) not null 
, quantidade smallint not null 
    , valor      moneynot null 
    , total      moneynot null ) 
 
GO 
 
create table exTrigger2 ( 
  codigo     char(13) not null 
, quantidade smallint not null 
    , valor      moneynot null 
    , total      moneynot null ) 
 
GO 
 
 
-- Execute estes inserts na tabela exTrigger2, vamos usar
-- mais pra frente.
 
insert into exTrigger2 (codigo, quantidade, valor, total) 
  select '1', 1, 1, 1 
 
insert into exTrigger2 (codigo, quantidade, valor, total) 
  select '2', 2, 2, 4 
 
insert into exTrigger2 (codigo, quantidade, valor, total) 
  select '4', 3, 3, 9
 

EXPLORANDO AS TABELAS INSERTED E DELETED

As tabelas INSERTED e DELETED, como comentado no post anterior, são tabelas “virtuais”, que existem somente durante a execução de uma trigger. Na tabela INSERTED temos os registros inseridos na tabela, ou os novos valores atribuídos aos registros alterados durante um update. Na tabela DELETED, o conceito é o mesmo, porém com os registros excluídos, ou no caso do update, os valores anteriores ao update. A trigger que estamos criando, será disparada sob qualquer operação de manipulação de dados que houver na tabela (INSERT, DELETE, UPDATE). Como foi falado anteriormente, você pode ter uma trigger configurada para disparar somente no evento INSERT ou somente no DELETE, mas aqui vamos colocar todas as operações para explorá-las melhor.

Lembrando que sistematicamente, todo processo executado em um sistema, geralmente possui um fluxo de retrocesso, estorno, ou algo similar para esta mesma operação. É neste momento que se deve tomar muito cuidado ao se criar trigger. Se você criou uma trigger para quando inserisse na tabela, movimentasse estoque, você também deve ter uma trigger que ao excluir o registro dessa tabela, estorne esta movimentação, caso contrário seu banco de dados não ficará consistente, e assim por diante.

A trigger que vamos criar possui o propósito específico de mostrar o comportamento das tabelas INSERTED e DELETED. Quando realizarmos operações na tabela exTrigger1, na tela de resultado da Query irá aparecer a contagem de registros incluidos, excluídos e alterados.

Vamos então criar nossa trigger na tabela exTrigger1:

Code Snippet
  
create trigger tgExTrigger1 on exTrigger1 
for insert, delete, update 
as 
begin 
 
 
   /*
   Autor: Fabiano Cores
   Data : 20/12/2010
   Site : http://sqlburger.blogspot.com
   Desenvolvido para demonstrar a utilização de 
   triggers no SQL Server. A reprodução deste 
   artigo só é permitida se forem mantidos os 
   créditos do autor.
   */
 
 
  declare @excluidos int 
  declare @incluidos int 
  declare @alterados int 
  declare @mensagem  varchar(100) 
   
  -- Quantidade de registros excluidos (delete) 
  select @excluidos = count(*) 
    from deleted 
   where codigo not in (select codigo 
                          from inserted) 
 
  -- Quantidade de registros incluídos (insert) 
  select @incluidos = count(*) 
    from inserted 
   where codigo not in (select codigo 
                          from deleted) 
 
  -- Quantidade de registros alterados (update) 
  select @alterados = count(*) 
    from inserted, deleted 
   where inserted.codigo = deleted.codigo 
    
  -- Monto a string de mensagem 
  select @mensagem = 
         'Incluídos: ' + rtrim(@incluidos) + char(13) + char(10) 
       + 'Excluídos: ' + rtrim(@excluidos) + char(13) + char(10) 
       + 'Alterados: ' + rtrim(@alterados) 
 
  -- Exibo na tela do Query Result os calculos. 
  print @mensagem 
 
end
--
 

EXECUTANDO OS TESTES

Agora que toda a estrutura de objetos está criada, vamos iniciar os nossos testes. Ao executar cada script, observe a estrutura do código da trigger e assimile ambos os códigos ao mesmo tempo, com calma, e irá entender com facilidade.

O primeiro teste que vamos realizar é um insert na tabela exTrigger1 (que possui a trigger). O script e a figura com o resultado seguem abaixo:

Code Snippet
 insert into exTrigger1 (codigo, quantidade, valor, total) 
  select 'A', 30, 10, 300
 

Agora, vamos fazer um update na tabela:

Code Snippet
 update exTrigger1 
   set quantidade = 20 
     , total = 200 
 where codigo = 'A'
 

E para finalizar a primeira parte dos exemplos, vamos excluir o registro que inserimos, e ver o resultado que a trigger irá imprimir na tela.

Code Snippet
  
delete from exTrigger1 
 where codigo = 'A' 
--
 

Agora, vou mostrar o por que criar uma trigger exige que sejam observados vários aspectos. Vamos agora utilizar os dados da tabela exTrigger2, que possui 3 registros que inserimos para teste. Vamos inserir dados em massa na tabela exTrigger1 (a que possui a trigger). Mas o que é inserir dados em massa? É quando inserimos em uma tabela o resultado de um select, que pode retornar mais de um registro. Vamos observar o que acontece:

Code Snippet
  
insert into exTrigger1 (codigo, quantidade, valor, total) 
 select codigo, quantidade, valor, total 
   from exTrigger2
--
 

Observe atentamente que inserimos 3 registros na tabela, porém a trigger foi disparada uma única vez. Muitas vezes, programadores amadores nem conhecem esta característica, ou as vezes o programador “acha” que só ocorrerão operações na tabela que incluam, alterem ou excluam apenas um registro, e é nesse momento que a trigger pode se tornar um problema. Na minha opinião, triggers são perigosas, e devem ser criadas a partir de muito planejamento e documentação. É importante lembrar que podem haver intervensões manuais na tabela, etc.

Um exemplo de como você pode seguramente tratar todos os registros de uma tabela, é utilizando um cursor:

Code Snippet
  
CREATE trigger tgExemplo on TabExemplo
for insert
as
begin
 
  declare @vcol1 int
  declare @vcol2 int
  
  declare cp1 cursor local fast_forward read_only for
    select col1, col2, ...
      from inserted
      
  open cp1
  
  while 1 = 1
  begin
    fetch next from cp1 into @vcol1, @vcol2
    if @@fetch_status <> 0 break
    
    -- sql_statements... 
        
  end
  close cp1
  deallocate cp1
 
end
--
 

O único conselho que insisto em descrever é que se tome cuidado ao criar uma trigger. Procure projetar todas as possibilidades de operações que possam ocorrer na tabela. Também é importante que, se você fez uma trigger que faz uma operação, provavelmente você tenha que fazer uma trigger inversa, que desfaça a operação. Exemplo: você tem uma trigger na tabela de apontamento de ordem de produção, que ao gravar o apontamento, a trigger realiza a baixa de estoque dos materiais da ordem de produção. Lembre-se que se você excluir este apontamento, você deverá fazer uma trigger de delete, que irá estornar o movimento do estoque, e assim por diante. E é aí que se encontra a importância de tratar multiplos registros inseridos ou excluídos.

No próximo capítulo, vamos tratar abordagens mais avançadas nas triggers, como desabilitalas temporariamente, definição de sequencia de disparo (multiplas triggers em uma única tabela), controle de redundância (a trigger modificar dados em sua própria tabela, disparando de forma cíclica a trigger), e outros conceitos muito úteis.

Até a próxima!

Triggers para iniciantes no SQL Server – Parte 1/3

2010-12-06T23:39:00.001-02:00

Este artigo se aplica ao Microsoft SQL Server a partir da versão 2000, porém o conceito de triggers se aplica à maioria dos sistemas gerenciadores de bancos de dados do mercado.

Olá caros sqlnautas. Neste artigo, trarei a vocês algumas dicas importantes sobre TRIGGERS no SQL Server. Este recurso nos possibilita disparar eventos programados ao se inserir, excluir ou alterar dados em uma tabela. Um exemplo de aplicação, que vamos abordar neste tópico, será a criação de uma trigger em uma tabela dos itens de uma nota fiscal. Quando os itens são gravados nesta tabela, automaticamente a trigger irá movimentar o estoque. Lembrando que nesta primeira parte, os scripts de exemplo foram desenvolvidos apenas para ilustrar. Na segunda parte, fornecerei scripts completos para realizar as simulações em seu próprio SQL Server.

CONHECENDO A ESTRUTURA DE UMA TRIGGER

Uma trigger (gatilho) é um bloco de código T-SQL (como um procedimento) que é executado no momento em que ocorre um INSERT, UPDATE e/ou DELETE na tabela onde foi criada.

Vamos então começar visualizando uma trigger. Para abrir uma trigger, podemos faze-lo de duas formas:

1. Localizar a trigger através do Object Explorer do Microsoft SQL Server Management Studio (imagem abaixo), clicar com o botão direito sobre ela e clicar em MODIFY (modificar).

2. Utilizando o comando sp_helptext

Vamos agora rapidamente olhar um código documentado de uma trigger completa (baixa estoque ao inserir itens na nota):

Code Snippet
 CREATE trigger tgiNotaItem on NotaItem 
for insert 
as 
begin 
  /* 
   
  Autor: Fabiano Cores 
  Data : 06/12/2010 
  Site : http://sqlburger.blogspot.com 
   
  Desenvolvido para demonstrar a utilização de  
  triggers no SQL Server. A reprodução deste  
  artigo só é permitida se forem mantidos os  
  créditos do autor. 
   
  */ 
 
  /* A tabela inserted existe "virtualmente" durante     
     a execução de uma trigger. Ela possui exatamente    
     a mesma estrutura da tabela da trigger, e contem    
     todos os registros inseridos no evento INSERT,      
     que disparou a trigger. */ 
 
  declare @vcd_empresa   smallint 
  declare @vnr_serie     char(3) 
  declare @vnr_nota      int 
  declare @vcd_item      char(13) 
  declare @vqt_faturada  decimal(10,4) 
  declare @vvl_unitario  money 
   
  declare cur_NF_Item cursor 
   local fast_forward read_only for 
    select cd_empresa 
         , nr_serie 
         , nr_nota 
         , cd_item 
         , qt_faturada 
         , vl_unitario 
      from inserted 
       
  open cur_NF_Item 
   
  while 1 = 1 
  begin 
    fetch next from cur_NF_Item into @vcd_empresa 
                                   , @vnr_serie 
                                   , @vnr_nota 
                                   , @vcd_item 
                                   , @vqt_faturada 
                                   , @vvl_unitario 
    if @@fetch_status <> 0 break 
 
 
    /* Para cada item que foi inserido, executo o 
       procedimento que movimenta estoque (só para 
       exemplificar) */ 
    exec spcMovimentaEstoque @vcd_empresa 
                           , @vnr_serie 
                           , @vnr_nota 
                           , @vcd_item 
                           , @vqt_faturada 
                           , @vvl_unitario 
     
     
  end 
  close cur_NF_Item 
  deallocate cur_NF_Item 
   
end
 

CABEÇALHO DE UMA TRIGGER

O primeiro parâmetro é o NOME (tgiNotaItem) da trigger, forma como o objeto será identificado no banco de dados. Quando uma trigger é criada, é gerado um registro na tabela SYSOBJECTS. Para se atribuir o nome da trigger, tenho como padrão colocar tgiTabela (trigger de insert), tgdTabela (trigger de delete), tguTabela (trigger de update), ou somente tgTabela, quando ambas as situações são contempladas no código.
O segundo parâmetro é o nome da tabela que será vinculada a esta trigger (no exemplo abaixo, estamos colocando um gatilho na tabela NotaItem).
O terceiro parâmetro (“for insert”) determina o tipo de trigger, que vamos entender melhor cada uma adiante.

Code Snippet
 create trigger trigger_name 
on { table_name } 
{ FOR / AFTER | INSTEAD OF }
 { [INSERT] [,] [DELETE] [,] [UPDATE] } 
as 
begin 
 
   sql_statement [ ...n ] 
 
end
 

Tipos de triggers

Existem 2 tipos de triggers no SQL Server (e na maioria dos SGBD do mercado).

AFTER: A trigger somente é executada após a gravação dos dados na tabela (padrão quando cria trigger)
INSTEAD OF: A trigger será disparada ANTES do registro ser modificado na tabela.

O tipo AFTER é o mais comum de ser utilizado. O tipo INSTEAD OF tem aplicações mais específicas. Os casos mais comuns em que utilizei este tipo de trigger foi para criar bloqueios em sistema que não detenho o código fonte (exemplo: lançamentos contábeis em período fechado, não permite gravar). Mais adiante falaremos mais sobre este tipo.

Exemplos:

AFTER

Code Snippet
 CREATE trigger tgiNotaItem on NotaItem 
FOR { insert | delete | update }
as 
 
OU
 
CREATE trigger tgiNotaItem on NotaItem 
AFTER { insert | delete | update }
as
 

INSTEAD OF

Code Snippet
 CREATE trigger tgiNotaItem on NotaItem 
INSTEAD OF { insert | delete | update}
as
 

Eventos de disparo de uma trigger

Uma trigger é disparada quando um evento de INSERT, DELETE ou UPDATE ocorre em uma tabela. Para isso, é necessário informar ao SQL que tipo de evento deseja tratar, que pode INSERT, DELETE ou UPDATE.

Exemplos:

Code Snippet
  
-- Trigger que será executada ao inserir um item no sistema     
CREATE trigger tgiNotaItem on NotaItem      
FOR insert
as 
 
  sql_statement [ ...n ] 
 
-- Trigger que será executada ao excluir um item no sistema
CREATE trigger tgdNotaItem on NotaItem 
FOR delete
as 
 
  sql_statement [ ...n ] 
 
-- Trigger que será executada em qualquer operação de manipulação     
-- de dados na tabela (insert, delete ou update)
CREATE trigger tgNotaItem on NotaItem 
FOR insert, delete, update
as 
 
  sql_statement [ ...n ]

TABELAS “DE AMBIENTE” EXISTENTES NA EXECUÇÃO DE UMA TRIGGER

Ao desenvolver uma trigger, você precisa ter acesso aos dados EM ATUALIZAÇÃO (no caso de insert, quais dados estão sendo inseridos, para exclusão quais estão sendo excluídos, e assim por diante). Para isso, chamo-as de “tabelas de ambiente”, que só existem no momento em que uma trigger está sendo executada.

Existe duas tabelas, chamadas INSERTED (registros incluídos ou alterados – novos valores) e DELETED (registros excluídos ou alterados – valores anteriores). Estas tabelas “virtuais” possuem exatamente a mesma estrutura da tabela em que a trigger foi criada. Vamos separar somente o trecho do código que busca dados desta tabela.

Exemplo:

Code Snippet
 CREATE trigger tgiNotaItem on NotaItem 
for insert 
as 
begin 
 
  ...
 
  declare cur_NF_Item cursor 
   local fast_forward read_only for 
    select cd_empresa 
         , nr_serie 
         , nr_nota 
         , cd_item 
         , qt_faturada 
         , vl_unitario 
      from inserted 
       
  open cur_NF_Item 
   
  ...
   
end
 

Observe que estou buscando dados de uma tabela INSERTED, porém se olhar esta tabela no Object Explorer, perceberá que ela não existe. Ela somente vai existir durante a execução de uma trigger. Os updates são ainda mais interessantes. Toda vez que um UPDATE é feito em uma tabela, durante a execução da trigger nós teremos na tabela DELETED quais eram os dados antes do update, e na INSERTED quais são os novos valores dos registros alterados.

Fique atento para o próximo capítulo da saga. Na próxima parte vamos entender mais sobre a parte prática, parâmetros e o comportamentos das triggers.

=)

SQL Dinâmico – usar código para criar código

2010-12-04T20:26:00.001-02:00

Hoje vou demostrar um pequeno exemplo de como utilizar programação dinâmica no SQL. No exemplo aqui demonstrado, utilizo o SQL dinâmico para definir a estrutura de uma tabela temporária de acordo com os dados que desejo exibir.

Eu montei rapidamente um modelo similar a um relatório que fiz recentemente. Eu precisava retornar os fornecedores como colunas, e doze meses como linhas, para montar uma grande planilha de dados. Para poder retornar o fornecedor na coluna, montei um loop nos fornecedores que deverão entrar no relatório, e utilizei o nome fantasia como nome da coluna que receberá o valor do mês/ano específico. Dentro deste mesmo loop, já busco o valor dos títulos deste fornecedor, e atualizo a tabela temporária. Depois só retorno os resultados.

Vamos então ver o código. Tentei comentar da melhor forma possível para explicar cada passo do procedimento.

create proc spcExemploSQLDinamico
as

   /*

   Autor: Fabiano Cores
   Data : 04/12/2010
   Site : http://sqlburger.blogspot.com

   Desenvolvido para demonstrar a utilização de querys
   dinâmicas no SQL Server. A reprodução deste artigo
   só é permitida se forem mantidos os créditos do autor.
   */

   declare @sql            varchar(8000)
   declare @vcd_cadastro   int
   declare @vfantasia      varchar(20)
   declare @valor          money
   declare @data           smalldatetime

   -- Crio a temporária que receberá modificações dinâmicas
   create table #tmp (
     mes tinyint null
   , ano smallint null )

   -- Preencho a tabela com as datas. Pego o ano atual,
   -- e defino como primeiro de janeiro, para pegar o ano
   -- inteiro.
   select @data = rtrim(year(getdate())) + '-01-01'

   set @sql = ''

   while 1 = 1
   begin

     -- Aqui estou montando uma query dinâmica. Veja que estou
     -- concatenando várias querys em uma variável VARCHAR, para
     -- posterior execução.
     select @sql = @sql
                 + 'insert into #tmp (mes, ano) '
                 + 'values ('
                 + rtrim(month(@data)) + ','
                 + rtrim(year(@data)) + ')'
                 + char(13) + char(10)

     -- Caso tenha inserido os 12 meses do ano, saio do loop
     if month(@data) = 12
       break

     -- A cada ciclo do loop, pulo um mês
     select @data = dateadd(month, 1, @data)

   end

   -- Aqui executo a query montada acima.
   exec (@sql)

   -- limitei em um TOP 25 para não trazer muitos registros
   -- O distinct é para que retorne apenas uma vez cada
   -- fornecedor neste select.
   declare cp1 cursor local fast_forward read_only for
     select distinct top 25 a.cd_cadastro
          , a.fantasia
       from cadastro a
          , fornecedor b
          , titulofor c
      where a.cd_cadastro         = b.cd_cadastro
        and a.situacao            = 'A'
        and a.cd_cadastro         = c.cd_cadastro
        and year(c.dt_lancamento) = year(getdate())
        and c.situacao           in ('A', 'L')
      order by a.fantasia

   /*
   As tabelas aqui utilizadas e parâmetros devem ser
   desconsiderados. Adapte este procedimento ao seu
   banco de dados.
   */

   open cp1

   while 1 = 1
   begin
     fetch next from cp1 into @vcd_cadastro
                            , @vfantasia
     if @@fetch_status <> 0 break

     -- Adiciono colunas na tabela com o nome do fornecedor
     select @sql = 'alter table #tmp add ['
                 + rtrim(ltrim(@vfantasia))
                                       + '] money null'

     exec (@sql)

     select @data = rtrim(year(getdate())) + '-01-01'

     while 1 = 1
     begin

       -- Sempre inicialize uma variável dentro de um loop.
       set @valor = 0;

       select @valor = sum(vl_titulo)
         from titulofor
        where situacao            in ('A', 'L')
          and cd_cadastro          = @vcd_cadastro
          and month(dt_lancamento) = month(@data)
          and year(dt_lancamento) = year(@data)

       select @valor = isnull(@valor, 0)

       -- Para montar o update, uso novamente o nome
       -- fantasia do fornecedor para saber qual o
       -- nome da coluna que preciso atualizar o valor.
       select @sql = 'update #tmp '
                   + '   set ['
                   + rtrim(ltrim(@vfantasia)) + '] = '
                   + rtrim(@valor)
                   + ' where mes = ' + rtrim(month(@data))
                   + '   and ano = ' + rtrim(year(@data))

       exec (@sql)

       if month(@data) = 12
         break

       select @data = dateadd(month, 1, @data)

     end


   end
   close cp1
   deallocate cp1

   select @sql = 'select * from #tmp order by ano, mes'

   exec (@sql)

O resultado é um relatório estruturado dinamicamente:

Afinal de contas, maiúsculo ou minúsculo?

2010-12-03T17:52:00.001-02:00

Este é apenas um pequeno post “desabafo”. Muitos programadores me questionam por que eu programo em SQL usando somente letras minúsculas. Vamos fazer uma pequena comparação de um código do jeito que eu faço, um no padrão que os programadores dizem ser o “correto”, e mais para o final, um terceiro procedimento, que é código interno do SQL.

Maneira como eu programo	Padrão considerado “correto”

Em primeiro lugar, minha influência em programação veio de meu pai, que sempre programou em C. Uma coisa inclusive que detesto no C++/C# é o fato de ser case sensitive. Isso é irritante! Hoje com as ferramentas atuais (visual studio por exemplo) isso “teoricamente” não é mais problema.

Bom, eu honestamente, acho uma grande besteira ficar ligando e desligando o CAPS LOCK enquanto se programa. Se o SQL tem o recurso de identificar todas as palavras do código por cor, pra você identificar comandos, strings, operadores, não vejo sentido nenhum nessa formatação. E pessoalmente, acho muito mais agradável e bonito o código sem letras maiúsculas, pra mim só poluem a tela.

Mas se ainda insistir em achar que eu estou errado, vamos dar uma olhadinha então nos códigos de programadores que desenvolveram o SQL! Sim, podemos ver esses códigos! Vamos pegar aqui, duas funções muito comuns do SQL (sp_help e sp_spaceused), e ver como é o código delas (basta usar sp_helptext para visualizar o código do procedimento):

exec sp_helptext 'sp_help'

exec sp_helptext 'sp_spaceused'

Se quiser ver no seu próprio SQL, é só executar “ sp_helptext 'sp_spaceused' ” ou “ sp_helptext 'sp_help' ” e você vai ver como foram feitos o código T-SQL dos procedimentos internos mais utilizados do SQL Server.

:D

Calculando o tamanho de cada tabela no SQL Server

2010-12-02T16:49:00.001-02:00

Hoje vou descrever uma solução que adotei durante uma visita a um cliente que estava com problemas de tamanho e performance no banco de dados. Ele utiliza Microsoft SQL Server 2005 para armazenar os dados do seu aplicativo. O problema é que precisávamos detectar tabelas que estavam grandes demais, e estavam absorvendo muitos recursos de armazenamento (tanto no servidor como no backup) e também recursos de processamento e memória. Só não confunda espaço do banco de dados com arquivo de LOG, que abordarei em um próximo artigo.

O trabalho teve várias etapas, porém vou descrever as que trouxeram maior resultado efetivo. Eu dividi o trabalho em duas partes. Primeiro identifiquei as tabelas que ocupando mais espaço. Depois, remodelei as tabelas de forma a utilizarem o espaço do banco de dados de forma mais adequada. Muitas vezes, um sistema utilizado em diversos segmentos, pode acabar tendo suas dimensões desproporcionais. Vou dar um exemplo muito simples. Você tem um sistema ERP que foi desenhado para indústrias, porém o implanta em uma distribuidora que emite 300 notas fiscais por dia, diferente do que havia sido previsto quando o sistema foi pensado para uma fábrica de médio porte. Neste exemplo, se as tabelas de notas fiscais conter campos mal dimensionados, ou relacionamentos redundantes, podem acarretar problemas de espaço e performance a longo prazo.

Neste artigo, vou abordar exclusivamente o cálculo do tamanho das tabelas. As modificações estruturais de tabelas, e demais otimizações, não vou abordar neste momento. O primeiro código que vamos analisar, é uma rotina que criei que relaciona todas as tabelas no banco de dados, calcula individualmente o espaço utilizado por cada uma e exibe no final um relatório ordenado pelas tabelas que mais utilizam espaço do banco.

sys.sp_spaceused

Este procedimento interno do SQL Server retorna informações sobre o tamanho do banco de dados, e também especificamente de tabelas passadas como parâmetro.

Code Snippet
             exec sp_spaceused-- retorna informações do banco
 
OU
 
exec sp_spaceused 'cadastro' -- retorna informações da tabela

RESERVED: Espaço total reservado pelo banco de dados “para armazenamento de dados”. Por mais que sejam excluídos registros do banco, ele nunca reduz o seu tamanho (a não ser que seja feito o Shrink Database, que irá devolver ao disco o espaço UNUSED do banco).

DATA: Quantidade em KB de dados no banco de dados

INDEX_SIZE: Tamanho utilizado somente para a indexação de valores. Muitos índices em um banco podem elevar consideravelmente o tamanho do banco, é necessário estar atento.

UNUSED: Espaço que foi alocado ao banco de dados, porém não está sendo utilizado. Este espaço pode ser retomado ao disco através da ferramenta Shrink Database.

Quando passamos o nome de uma tabela como parâmetro, obtemos a mesma informação, porém os valores informados especificamente da tabela, com a diferença que temos uma coluna a mais, chamada ROWS, que retorna o número de linhas nesta tabela:

Utilizando esta ferramenta do sistema, criei o seguinte procedimento:

Code Snippet
 create proc sp_espacotabela
as
 
/*
  Fabiano Cores 02.12.2010
  http://sqlburger.blogspot.com
*/
 
  declare @vname sysname
  declare @tmpTamTabela table (
    name       sysname     null
  , rows       int         null
  , reserved   varchar(25) null
  , data       varchar(25) null
  , index_size varchar(25) null
  , unused     varchar(25) null )
 
  declare cp1 cursor local fast_forward read_only for
    select name
      from sysobjects
     where type = 'U'
     order by name
 
  open cp1
 
  while 1 = 1
  begin
    fetch next from cp1 into @vname
    if @@fetch_status <> 0 break
 
    insert into @tmpTamTabela (name, rows, reserved
                             , data, index_size, unused)
      exec sp_spaceused @vname
 
  end
  close cp1
  deallocate cp1
 
  select name as 'Nome'
       , rows as 'Linhas'
       , convert(int, replace(reserved, ' KB','')) as 'Tamanho total'
       , convert(int, replace(data, ' KB',''))as 'Dados'
       , convert(int, replace(index_size, ' KB',''))as 'Index'
       , convert(int, replace(unused, ' KB',''))as 'Não utilizado'
    from @tmpTamTabela
   order by convert(int, replace(reserved, ' KB','')) desc
 

O código acima já está preparado para ser salvo no banco de dados, através do nome sp_espacotabela. Vamos ver agora sua execução, e o resultado apresentado:

O resultado é um relatório que retorna todas as tabelas, e os respectivos valores de consumo de tamanho em disco, conforme explicado acima. Você pode exportar esta lista para o excel, e fazer várias visões, como analisar índices x quantidade de linhas, ordenar por espaço não utilizado, etc). Observe que pode ser modificado o último SELECT do procedimento, para ordenar por outro valor. Eu optei em ordenar pelo tamanho total da tabela (RESERVED), que é o que importa nesta análise.

Após visualizar o relatório, pegamos as 10 tabelas mais inchadas do banco (de um banco com aproximadamente 2500 tabelas). Geralmente são as tabelas de LOG, notas fiscais, cupons, etc. Observamos a presença de campos tipo TEXT desnecessários (para armazenar média de 80 a 180 caracteres). Mudamos para CHAR(250), e assim por diante. Com uma pequena modificação como esta, chegamos a otimizar 3GB de uma única tabela (as imagens utilizadas aqui são somente para exemplificar).

Outras tabelas, como notas fiscais e lançamentos financeiros, fizemos uma espécie de datawarehouse, deixando na base de dados de trabalho 3 anos de histórico, os demais anos foram separados em uma base onde os relatórios consolidam estes bancos. A transição dos dados é feita automaticamente a partir de uma JOB, sem a necessidade de intervenção do usuário. O backup do período anterior fica intacto, armazenado em mídia separada. O backup feito diariamente passou a ser dos últimos 3 anos.

Todas estas modificações trouxeram grandes benefícios, como otimização dos backups, mais velocidade em processos críticos do sistema (faturamento, log), banco de dados mais enxuto, sem alocar grandes espaços desnecessários, e assim por diante.

Resultados

No final, aplicando as técnicas aqui descritas, e outras manutenções, o banco de dados de produção, que estava com 13,5GB de tamanho, ficou com modestos 1,8GB. O backup, que é totalmente automatizado, estava levando mais de 5 horas para ser realizado, pois o banco compactado ficava com 3,0GB. Levava 1 hora para compactar, e 4 horas para transmitir. E se houvessem quedas de internet durante a transmissão, esta tinha que ser feita manualmente. Hoje, o arquivo de backup compactado está com 122mb, leva 2 minutos para ser gerado e 20 minutos para ser transmitido.

Como tratar valores DATA (datetime) no SQL SERVER

2010-11-28T20:32:00.001-02:00

Este artigo se aplica a qualquer versão do Microsoft SQL Server a partir da versão 2000.
TAGS: #DATEADD, #DATEPART, #GETDATE, #BISSEXTO, #SQL, #SQLSERVER

O SQL possui inúmeras ferramentas para o tratamento de datas. Aqui vou apresentar algumas dicas que considero as mais relevantes, que poderão ajudá-lo no desenvolvimento de suas querys.

CALCULAR DATA INICIAL E FINAL A PARTIR DO MÊS/ANO

Você está programando um código que utiliza uma procedure que já existe no banco de dados. O parâmetro para o cálculo que você possui é o mês e o ano, porém esta procedure recebe o parametro @data_inicial e @data_final. Como você faz para chamar este procedimento, se você só tem o mês e o ano?

Bom, o macete que vou apresentar aqui é muito simples. Vamos utilizar a função DATEADD. A função DATEADD permite adicionar ou subtrair valores específicos de uma data. Então, para começar, montamos a data inicial:

select @dt_inicial = rtrim(@ano) + '-' + right('00' + rtrim(@mes),2) + '-01'

Nesta query, estamos concatenando o ano, o mês e o dia 01 fixo para o @mes e @ano que temos como parâmetro. O código right(‘00’ + rtrim(@mes,2) serve para sempre retornar o mês com dois dígitos (01, 05, 11, 12). O formato da data que devemos montar é YYYY-MM-DD.

Após calcularmos a data incial, vamos utilizar a função DATEADD duas vezes. Vamos somar um mês e subtrair um dia, nesta sequencia, para descobrirmos o último dia do mês:

select @dt_final = dateadd(day, -1, dateadd(month, 1, @dt_inicial))

A query dateadd(month, 1, @dt_inicial) retorna a data inicial com 1 mês a mais, e a função dateadd(day, –1, … ) pega este valor e subtrai um dia, desta forma teremos o último dia do @mes e @ano.

Segue abaixo o código completo para que possa copiar:

Code Snippet
 --
 
-- @mes = 3, @ano = 2010
declare @mes smallint
declare @ano smallint
declare @dt_inicial smalldatetime
declare @dt_final smalldatetime 
 
select @mes = 2, @ano = 2012 
 
select @dt_inicial = rtrim(@ano) + '-'
                   + right('00' + rtrim(@mes),2) + '-01' 
 
select @dt_final = dateadd(day, -1, dateadd(month, 1, @dt_inicial)) 
 
select @dt_inicial as 'Data inicial', @dt_final as 'Data final'
 
                    
 --
 

CÁLCULO DE ANO BISSEXTO

Já vi programadores fazerem cada código bizarro para descobrir se o ano é bissexto. Primeiro, que eu realmente não vejo utilidade em saber se o ano é bissexto ou não. Ah, é para definir uma variável data com o último dia do mês, mas não sabe se termina com 28, 29, 30 ou 31 dias? Esqueça isso, utilize a dica que mostrei acima. Mas de qualquer forma, o truque para calcular se um ano é bissexto é que o resultado da divisão do ano por 4 (quatro) não pode ser maior que zero. No SQL, para se descobrir o resto de uma divisão, utilizamos o operador MOD (%). Veja a query abaixo:

Se o cálculo @ANO % 4 = 0, significa que o ano é bissexto, assim como no exemplo acima (ano 2012).

UTILIZE A FUNÇÃO DATEPART e CONVERT PARA OBTER QUALQUER VALOR DE UMA DATA ESPECÍFICA

Existem casos em que precisamos extrair o ano de uma data específica, ou hora e minutos, e assim por diante. Vou apresentar alguns exemplos úteis de utilização da função DATEPART e CONVERT.

1. Pegando valores individuais de uma data (dia, mes, ano, hora, minutos e segundos) –

Code Snippet
 --
 
declare @dia smallint, @mes smallint, @ano smallint
      , @hr  smallint, @min smallint, @seg smallint
 
select @dia = datepart(day, getdate())
     , @mes = datepart(month, getdate())
     , @ano = datepart(year, getdate())
     , @hr  = datepart(hour, getdate())
     , @min = datepart(minute, getdate())
     , @seg = datepart(second, getdate()) 
 
select getdate() as 'Getdate() original'
     , @dia as 'Dia'
     , @mes as 'Mes'
     , @ano as 'Ano'
     , @hr  as 'Hora'
     , @min as 'Minuto'
     , @seg as 'Segundo'
 
                    
 --
 

* A função GETDATE() retorna a data e hora atual do servidor SQL.

2. Os tipos de CONVERT mais comuns com datas (#SQL = formato de data padrão do SQL YYYY-MM-DD).

Code Snippet
 --
 
select getdate() as 'Getdate() original'
     , convert(char(10), getdate(), 120) '#SQL'
     , convert(char(10), getdate(), 103) 'PT-BR'
     , convert(char(10), getdate(), 101) 'US-EN'
     , convert(char(8), getdate(), 14) 'TEMPO-FULL'
     , convert(char(5), getdate(), 14) 'TEMPO-SHORT'
 
 --
 

sysobjects, sys.objects, syscolumns e syscomments

2010-11-26T22:21:00.001-02:00

Utilize as tabelas de sistema do SQL Server para turbinar seu trabalho

As tabelas de sistema contém informações sobre a estrutura do seu banco de dados. Estas estruturas permitem a criação de dinâmicas de código que facilitam o dia-a-dia do programador SQL. Todas estão disponíveis a partir do SQL 2000 (com excessão da tabela SYS.OBJECTS, disponível a partir da versão 2005). Vamos conhecer as tabelas.

SYSOBJECTS
Esta tabela armazena todos os objetos do banco de dados, tais como tables, procedures, triggers, views, functions, foreign keys, constraints, entre outros. Resumindo, todos os objetos de usuário estão nesta tabela. As principais colunas da tabela são:

Name: nome do objeto no banco de dados
crdate: data de criação do objeto
type: tipo do objeto.

Alguns exemplos de aplicação:

Code Snippet
 --
 
select name, crdate, type 
  from sysobjects
 where name like 'spcFAE%'
 order by crdate desc
 
 --
 

Retorna o nome, data de criação e tipo dos objetos que o nome comece com “spcFAE”, ordenando por data de criação descescente.

Code Snippet
 --
 
select name, crdate, type 
  from sysobjects
 where crdate > '2010-05-01'
 order by crdate desc
  
 --
 

Retorna todos os objetos criados no banco de dados a partir de 01 de maio de 2010.

Code Snippet
 --
 
select name
  from sysobjects
 where type in ('P','TR','FN')
 order by crdate desc
  
 --
 

Retorna o nome dos procedimentos (P), triggers (TR) e functions (FN).

Estas pesquisas na tabela SYSOBJECTS são muito úteis, por exemplo, para buscar todos os procedimentos criados a partir de uma determinada data, ou relacionar rotinas por nome, etc. Existe ainda (somente a partir da versão 2005 do SQL), a tabela SYS.OBJECTS, que funciona como uma extensão da tabela SYSOBJECTS (relaciona pela coluna ID), porém possui uma coluna que é muito útil para desenvolvedores, a coluna MODIFY_DATE. Esta coluna armazena a data da última alteração de um objeto.

Imagine a seguinte situação, você trabalha 5 dias corrigindo em uma base teste dezenas de procedimentos. Você não precisa mais ficar anotando qual rotina alterou para criar o script no final, basta buscar da tabela sys.objects os objetos onde o modify_date seja maior ou igual ao dia que começou a trabalhar… bacana não é?

Os tipos conhecidos mais comuns de objetos na tabela (coluna type) são:

U - TABLE (tabela)
F - FOREIGN KEY (chaves estrangeiras)
C - CHECK CONSTRAINT
D - DEFAULT
P - STORED PROCEDURE
V - VIEW
TR – TRIGGER
TF - TABLE FUNCTION
FN - USER DEFINED FUNCTION
S - SYSTEM TABLE
K - PRIMARY KEY (chave primária)

A tabela sysobjects também pode ser utilizada em conjunto com outras tabelas de sistema, para outros tipos de pesquisa no banco de dados. Vamos conhecer agora a tabela SYSCOLUMNS.

SYSCOLUMNS
Esta tabela armazena armazena as colunas de tabelas, parametros de entrada de procedimentos, e assim por diante. Toda vez que é criada uma nova tabela ou procedimento no sistema, o SQL grava nesta tabela informações preciosas, que podemos utilizar a qualquer momento. As colunas que mais vamos utilizar neste exemplo são:

ID	é a identificação do objeto a quem pertence estas colunas [ syscolumns.id = sysobjects.id ]
xtype	determina o tipo de coluna (inteiro, decimal, char, varchar, bit, etc). O tipo é um código, encontrado em systypes [ syscolumns.xtype = systypes.xtype ]
length	tamanho do campo (no caso char, varchar terá o tamanho específico).
xprec & xscale	especifico para tipos decimais, informando a quantidade total de dígitos com decimais (xprec), e a quantidade de decimais (xscale)
allownulls	valor bit (0/1) que informa se a coluna aceita valores nulos ou não.
collation	Especifica o COLLATION da coluna (para tipos texto). Isto é necessário para solucionar problemas de conflitos de bancos de dados de diferentes idiomas.

A partir desta tabela, é possível ler qualquer estrutura do banco de dados. Qual a utilidade disso? Como uma pequena demonstração do que podemos fazer com a tabela SYSCOLUMNS, vamos criar um procedimento de apoio (?). Bom, um procedimento de apoio é um código feito para gerar código. É isto mesmo!

Vamos exemplificar.

Temos uma tabela chamada NotaItemLote (tabela onde são gravados os números de lote dos itens de uma nota fiscal). Você está criando uma rotina que irá inserir dados nesta tabela (gerando uma nota fiscal). Conhecendo a dinâmica de bancos de dados, quando construo uma query de insert, eu sempre especifico o nome dos campos da tabela que estou inserindo dados:

Code Snippet
 --
 
insert into tabela (coluna1, coluna2, coluna3) select 1, 2, 3 
  
 --

Isto garante que no caso de uma eventual atualização no sistema, se forem adicionadas colunas novas nesta tabela que aceitem nulo, minha rotina ficará estável e não dará erros. Se você não especifica o nome das colunas no insert, ao alterar a estrutura da tabela, todas as rotinas que geram insert sem especificar o nome darão erro. Isto é padronização básica da básica.

Pois bem, como vamos gerar um insert então? Muito simples! Altere o nome da tabela para uma que exista no seu banco de dados, e veja o resultado do select abaixo (utilizando syscolumns)

Code Snippet
 --
 
select syscolumns.name + ', '
  from syscolumns, sysobjects
 where syscolumns.id = sysobjects.id
   and sysobjects.name = 'NotaItemLote'
 order by syscolumns.colorder
  
 --
 

A query irá retornar o nome das colunas da tabela, em sequência, e ainda colocando uma virgula ao nome do lado, para facilitar. Aí basta copiar, colar e montar a sua query de insert

Code Snippet
 --
 
insert into NotaItemLote (cd_empresa, nr_serie, nr_nota, cd_item
                        , nr_lote, dt_validade, qt_lote, sinal) ...
  
 --
 

Então vamos melhorar mais ainda, e facilitar a utilização deste procedimento, pois não teria sentido digitar uma query como essa toda vez que quisesse pegar as colunas de uma tabela. Então vamos criar um procedimento chamado sp_gerainsert. Segue o código dele abaixo:

Code Snippet
 --
 
create proc sp_gerainsert
  @objname as sysname
as
 
  select name + ', '
    from syscolumns
   where id = object_id(@objname)
   order by colorder
     
 --
 

*A função object_id retorna o ID do objeto passado como parâmetro (o mesmo ID da tabela sysobjects).

Após compilar o procedimento, vá até o menu TOOLS (ferramentas), OPTIONS (opções). Selecione KEYBOARD, e insira a função que criamos no atalho CTRL+5 (pode vincular em qualquer atalho que desejar):

Agora, toda vez que for criar uma query de insert em uma tabela, basta selecionar o nome da tabela na janela de query, e pressionar CTRL+5 (ou o atalho que tenha especificado):

SYSCOMMENTS
A tabela syscomments é onde ficam armazenados todos os textos de procedimentos, triggers, funções, etc. Quando você executa um CREATE PROCEDURE, por exemplo, é nesta tabela que fica armazenado todo o código da procedure que compilou.

Vamos então para a parte prática. Digamos que você esteja fazendo uma atualização no seu banco de dados. Uma tabela de uso frequente recebeu uma nova coluna, e esta coluna não aceita valores nulos. Isto significa que você precisa revisar todos os procedimentos que inserem dados nesta tabela, correto? Para entender a forma como vamos procurar isso na tabela syscomments, vamos primeiro analisar uma query de insert:

Code Snippet
 --
 
insert into NotaItemLote (cd_empresa, nr_serie, nr_nota, nr_lote
                        , dt_validade, qt_lote, sinal)
  select 1, 'NFF', 1525, 'A0001', '2010-12-31', 10.0 , 'E'
     
 --
 

Muito bem, o que precisamos fazer então, é localizar todos os procedimentos, triggers ou funções que contenham o seguinte texto: “insert into NotaItemLote”. Vamos então montar a query de busca na syscomments da seguinte forma:

Code Snippet
 --
 
select name
  from sysobjects
 where id in (select name
                from syscomments
               where text like '%into notaitemlote%')
                    
 --
 

O resultado da query é o nome de todos os procedimentos que contenham este texto “into notaitemlote”. Quem está acostumado com atualizações em bancos de dados que contém procedimentos, conhece bem esta rotina.

Estas dicas, aplicadas no dia-a-dia, garantem muito mais produtividade ao programador, pois não precisa ficar digitando nome de colunas, localiza os objetos alterados no sistema, etc. É muito mais controle sobre os objetos para o programador. E pode ter certeza, utilizar estes indicadores (como data de criação, data de alteração da rotina, etc) garantem muito mais precisão do que documentação manual.

Você já está coletando?

2010-11-24T23:14:00.001-02:00

Soluções portáteis de baixo custo agregam alto valor operacional.

Gostaria de compartilhar um de meus projetos recentemente implementado. O case é da empresa Inovadoor Portas Industrias Ltda, localizada em Curitiba/PR. O objetivo do projeto era melhorar a operação do almoxarifado e da produção no sistema. O usuário perdia muito tempo sentando na frente do computador para pagar cada uma das dezenas de requisições que recebia diariamente.

Diante de centenas de diferentes itens, diferentes códigos e embalagens, o procedimento de controle de estoque também era complexo, exigia um profissional especializado para manter as informações em dia.

Nosso projeto foi dividido em duas partes importantes:

Identificação do estoque

O primeiro passo foi adquirir uma impressora térmica para emissão de etiquetas.

A impressora de etiquetas (modelo utilizado: Argox OS-214 Plus) funciona através de transferência térmica para produção de etiquetas com textos, logotipos e código de barras. Uma solução ideal de baixo custo para pequenas e médias tiragens, utilizando mínimo espaço. Possui um sistema completo de gerenciamento de memória que permite o armazenamento controlado de formatos de etiquetas, gráficos e fontes. Além disso, tem incorporado uma poderosa linguagem de comandos para geração e controle dos formatos (PPLA).

Utilizando Visual Studio 2008, desenvolvi uma aplicação em Visual Basic que busca do banco de dados (no caso MS SQL Server) os itens, descrição, unidade, entre outras informações, para serem estampadas nas etiquetas. Desta forma, o almoxarifado foi totalmente identificado. O código de barras escolhido foi CODE 39, pois os códigos de itens são alfanuméricos (00.000.000).

Custo aproximado: R$ 800,00 impressora + ribbon + etiqueta (preferencialmente com customização de logo)

Coletor de dados Argox PT-60

Buscando uma solução de baixo custo, porém que atendesse a todas as necessidades do projeto, chegamos ao Argox PT-60.

O coletor de dados PT-60 possui o sistema operacional Windows CE 5.0. Suas características são er gonomia, display colorido de 2.4 polegadas TFT QVGA 320 x 240 display com touch screen, alto desempenho para leitura de código de barras (leitor CCD), comunicação sem fio tais como Bluetooth e a tecnologia 802.11 b/g, interface USB e RS-232(serial). Possui slot para cartão SD para expansão de memória.Leve, pequeno e resistente, possui como opcional o pistol grip, para maior facilidade de manuseio.

Custo aproximado: R$ 2.900,00 (com pistol grip + bateria auxiliar)

Características

Potência
Bateria: recarregável padrão 3.7V 1950mAh Li-ion - recarregável com Pistol Grip: 3.7V 4400mAh Li-ion
Autonomia: 8 horas com bateria padrão e 12 horas com bateria do Pistol Grip.
Retenção de Dados: Após removida a bateria, os dados são armazenados por 15 minutos.
Dimensões
Medidas: 169 mm (L) x 70.1 mm (W) x 34 mm (H)
Peso: 250g (com baterias)
Especificações Gerais
Velocidade de Leitura: 400 scanners/segundos
Distância de Leitura: 600mm (code 39, 20 mils, PCS 90%)
Sistema de Leitura: 2048 scanners pixels
CPU: ARM9 Core 266MHz
Memória: 128Mb de Flash ROM, 128Mb DDRAM
Tela: 2.4 polegadas LCD TFT, 320 x 240 QVGA com touch screen
Teclado de Borracha: 29 Teclas (Numérico)
Ângulo do Leitor: 0° a 45° graus
Alarmes
Indicador por Beep: Programável nas freqüências de 1KHz a 4KHz
Indicador com LED: Azul, verde e vermelho
Fonte de Luz: Visível operando em 670±15 nm
Ambiente
Temperatura de Operação: -10ºC a 60º C
Temperatura de Armazenagem: -20º C a 70º C
Indice de Proteção: IP54 (imunidade contra poeira e contra respingos, projeções d´água e jatos leve d´água)
Umidade: 5% a 95% (sem condensação)
Resistência a Queda: 1.20 m de queda livre sobre concreto
Regulamentação de Emissão Eletromagnética: FCC, CE, BSMI, CCC
Comunicação
Interface RS-232: Taxa de até 115.2k bps
Interface IrDA: Compatível com Standard 1.0, até 115.2k bps - High Speed IR Taxa de transmissão até 115.2k bps
Programação
Linguagem: embedded Visual C++ 4.0, Visual Studio.NET, Visual Studio 2005, Windows CE SDK Scanner Configuration Utility
Acessórios
Inclusos: Bateria 1950mAh, Cabo Carga & Comunicação USB, Fonte de Alimentação, Caneta Stylus, Alça de mão

Garantia 12 Meses

*fonte www.hardstand.com.br (http://www.hardstand.com.br/coletor-de-dados-argox-pt60.htm)

FOTOS DO EQUIPAMENTO

Para a transmissão dos dados, foi adquirido um aparelho ROTEADOR WIRELESS DLINK DI-524 e instalada uma antena mais potente, de 12dbi, localizada bem na entrada do almoxarifado.O sinal de transmissão em todos os locais do almoxarifado é de sempre 80% a 100%.

Custo aproximado: R$ 100,00 roteador + R$ 50,00 antena.

Software do coletor

Através do Microsoft Visual Studio 2008, desenvolvi o aplicativo para o coletor, contendo as seguintes operações:

Consumo
Permite ao almoxarife dar baixa do estoque. Ao clicar a tecla 1 (ou pressionar a opção na tela touch screen), basta ler o código de barras do item, e informar a quantidade. A movimentação do estoque é realizada no banco de dados do sistema.

Requisição
Funciona exatamente como o apontamento de consumo, porém solicita o número da requisição de materiais no começo para vincular as baixas à respectiva ordem de produção.

Ajuste
Ferramenta que permite ao almoxarife realizar ajustes no estoque, como inventário. Com o coletor em mãos, realiza a contagem do material e já faz a entrada do saldo, utilizando o código de barras.

Compra
Permite ao almoxarife solicitar a compra de qualquer material utilizando apenas o código de barras da etiqueta.

Consulta
Exibe na tela relatório de razão de estoque analítico do item, assim como saldos em depósitos alternativos, afim de analisar a disponibilidade do material.

Através destes 5 recursos, o almoxarifado é capaz de processar qualquer operação no sistema de gestão sem precisar se deslocar até o computador, assim como em qualquer lugar da fábrica pode consultar saldos, gerar pedido de compra, etc. O ganho operacional é gigantesco.

Valor aproximado do projeto

Impressora térmica	+/- R$ 800,00
Ribbon e etiquetas (50 rolos)	+/- R$ 200,00
Coletor de dados Argox PT-60	+/- R$ 2.900,00
Roteador D-Link DI-524	+/- R$ 100,00
Antena 12dbi	+/- R$ 50,00
*Total*	*+/- R$ 4.050,00*

O projeto teve aproximadamente 30 dias de duração, e já está em pleno funcionamento. Agora estamos iniciando o trabalho de expansão, através da aquisição de mais equipamentos para redundância, e adição de novas funções, como controle de expedição, apontamentos da produção, etc.

Interpretando código SQL de forma eficiente (segmentada)

2010-11-24T19:31:00.001-02:00

Para este artigo, estou utilizando o Microsoft SQL Server 2008, porém se aplica a qualquer gerenciador de banco de dados.

Em muitos treinamentos que prestei, ou até mesmo acompanhando outros profissionais que ainda estão em estágio de aprendizado, percebo uma enorme dificuldade em conseguir enxergar realmente o que são todas aquelas pernas dentro de um único select. É left join pra cá, distinct pra lá, group em cima, e order em baixo. Existem select’s que chegam a ultrapassar páginas de código, para uma única consulta. E isso é muito comum, pois uma programação limpa no SQL consiste em não utilizar cursores, loops, tabelas temporárias, usando somente em casos muito específicos. O ideal é reduzir o custo das rotinas, reduzindo o número de operações no banco de dados.

O exemplo que vamos estudar nesta publicação é um trecho de uma Stored Procedure responsável por gerar o faturamento a partir de uma ordem de serviço (ADOOS). Este faturamento gera nota fiscal, títulos a receber, entre outras informações. De forma a minimizar o custo total da query, uni em um único select todos os dados necessários para executar este faturamento. Esta união resultou em um select que busca ao mesmo tempo, dados de 12 diferentes tabelas. O que vamos entender, é como fazer a interpretação de uma query extensa, utilizando a técnica de segmentação do código.

Vamos dar uma olhada no trecho do código:

Esta é uma query de nível fácil a intermediário, pois todas as tabelas possuem relacionamento um para um. Veja que utilizei apelidos para as tabelas (cadastro – e, cidade – f, cidade – g), repeti as tabelas CADASTRO e CIDADE pois preciso dos dados de cadastro e estado tanto do cliente quanto da empresa.

Se para você, esta query é assustadora, então você está no post certo!

Em primeiro lugar, e não sei por qual razão, a maioria dos programadores iniciantes NÃO LÊEM. É isso mesmo, não lêem o que está escrito na tela. Uma query exige uma leitura muito mais minuciosa que um texto, pois o SQL tem que ser exato, todos os laços devem ser fechados para que a query rode corretamente. É matemática, teoria dos conjuntos.

O primeiro passo, é identificar cada tabela, os dados que serão extraídos dela e qual o seu relacionamento com as demais.

ADOOS – Quando esta rotina é chamada, recebe como parâmetro o número da ordem de serviço. A tabela ADOOS é onde ficam armazenadas todas as ordens de serviço, contendo data de abertura, cliente, tipo de ordem de serviço, etc. A chave primária desta tabela é EMPRESA, SÉRIE E NR_OS.

ADOOSOutra – Tabela auxiliar à tabela principal, traz informações adicionais como programação de entrega, plano gerencial, centro de custo, etc. O seu relacionamento é de 1x1 (um registro em ADOOS para um registro em ADOOSOutra). Mesma chave primária que ADOOS.

ADOOSEncerra – Assim como no exemplo acima, esta tabela também é 1x1, tendo a mesma chave primária que a ADOOS.

Cadastro (d) – contém os dados de cadastro do cliente, pois na tabela ADOOS somente é especificado o código.

Cadastro (e) – contém os dados da EMPRESA que emitiu a ordem de serviço, a tabela ADOOS se relaciona com esta tabela através da chave EMPRESA.

Cidade (f) – Tabela que será relacionada à tabela Cadastro (d), para buscar o código do estado (UF) do cliente.

Cidade (g) – Tabela que será relacionada à tabela Cadastro (e), para buscar o código do estado (UF) da empresa.

Condicaovenda (h) – Tabela que contém informações inerentes à condição de venda estabelecida na tabela ADOOS (coluna cd_condicaovenda).

As demais tabelas não precisamos colocar neste exemplo, já temos o suficiente. Bom, já identificamos o papel de cada tabela. Este trabalho pode ser realizado lendo a estrutura de cada tabela, fazendo consultas, consultar a estrutura no modo design, e assim por diante. Até aqui não tem segredo, certo?

Depois que nós concluímos este levantamento, vamos então analisar todas as referencias na parte WHERE, onde estabelecemos os relacionamentos entre as tabelas:

ADOOS ~> É a tabela que nos serve de parametro, pois temos o número da ordem de serviço. É ela que receberá a comparação com os parâmetros recebidos pelo procedimento:

ADOOSOutra ~> Se relaciona com a tabela ADOOS através das chaves primárias. Já identificamos seu relacionamento.

ADOOSEncerra ~> mesmo caso da ADOOSOutra, relacionamento 1x1 através das chaves primárias.

Tabelas de cadastro e respectivas cidades ~> veja que o código de cadastro da tabela A (ADOOS) é igual ao código de cadastro da tabela D (CADASTRO DO CLIENTE). O código da cidade F é igual ao código da cidade da tabela CADASTRO DO CLIENTE D. Também relacionamos o código da empresa que emitiu a OS com a tabela CADASTRO E, que também relaciona o seu respectivo código de cidade com a tabela CIDADE G.

Leia atentamente o código. Nunca observe a query como um todo, leia de forma segmentada. Primeiro veja as tabelas, se concentre em entender qual o mapeamento de dados responsável por cada uma. Depois, identifique cada relacionamento entre as tabelas. Veja item por item, para identificar possíveis erros de JOIN (relacionamento). Mas é necessário que seja observado item por item. Importante lembrar para que sempre leia atentamente as mensagens de erro durante execuções no sql. Muitas vezes, ao executar um procedimento, o usuário vê a mensagem vermelha de erro, e imediatamente coloca na cabeça: deu erro, vou procurar… mas nunca lê a mensagem, que diz exatamente onde está o problema! Digo isso por que tive esta experiência com uma pessoa que está aprendendo nesta semana *-*

\o/

O truque do zero à esquerda

2010-11-23T23:47:00.001-02:00

Technorati Marcas: Microsoft SQL Server,Programação,Development,SQL,String,Rtrim,Right

Olá caros sqlnautas. Este artigo é relacionado à T-SQL em Microsoft SQL Server. Este artigo é compatível com qualquer versão a partir do SQL Server 2000.

Existem algumas situações, em que precisamos concatenar zeros à esquerda de um número para transformar em string, geralmente necessário quando estamos fazendo layouts de arquivos de texto para exportação entre sistemas.

Exemplo:

O número 25 deve retornar como 00025, assim como o número 325 deve retornar 00325 (mesmo número de algarismos).

Mas como resolver isso facilmente? Muito simples!

Code Snippet
 declare @numero int
declare @texto varchar(100)
 
select @numero = 23
 
select @texto = right('00000' + rtrim(@numero),5)
 
select @texto as [NTexto]
 

O resultado, não pode ser diferente:

Veja no exemplo abaixo, se o mês fosse janeiro ou outubro, ambos ficariam corretos com a concatenação do zero à esquerda:

A lógica é muito simples.

RTRIM ~> utilizado para remover espaços a direita de uma string, porém quando usado em um valor inteiro, o transforma em string.

RIGHT(str, qtd) ~> Pega os caracteres à direita da string informada conforme quantidade especificada.

“rtrim(@numero)” ~> transforma o valor da variavel @numero (inteiro) em string;

”right(‘00000’ + rtrim(@numero),5) ~> concateno ao número convertido em string o número fixo de zeros a esquerda que desejo (neste caso, estou utilizando 5 zeros), e depois corto à direita 5 digitos.

O número 25 ao concatenar os zeros fica “0000025” e com o right(str, 5), deixa somente 00025.

Esta técnica garante que independente do número, ele sempre irá preencher a quantidade correta de zeros a esquerda. Lembre-se de dimensionar corretamente a quantidade de zeros, pois se o número for maior que o limite, não ocorrerá erro, porém o número retornado será truncado. (exemplo: se está trabalhando com 5 zeros, e utilizar esta técnica no número ‘123456’, retornará à string somente ‘23456’)

That’s all folks!