SQL GO!!!

select 'Blog focado em SQL Server' from blog where nome ='SQLGO.wordpress.com'

Arquivos de tags: Virtual PASS BR

Usando o comando MERGE

 

Um dos novos comandos do SQL Server 2008 é o merge, com esse comando podemos comparar registros entre duas fontes de dados (origem e destino). Você pode utilizar as ações de Insert, Update e Delete baseados no resultado da comparação entre as duas fontes.

 

Sintaxe:

INTO <target_table>Define a tabela ou view que será a fonte de destino.

AS – Define um alias.
 
USING <table_source> – Define a tabela ou view que será fonte de origem, baseado na condicao de merge da tabela destino com a tabela de origem.
 
ON <merge_search_condition>Define as condições de “Join” da tabela destino com a tabela origem.
 
WHEN MATCHED THEN – Define a ação a ser realizada quando há linhas na fonte de destino origem que correspondem com a fonte de origem.
 
WHEN NOT MATCHED [ BY TARGET ] THEN – Define a ação a ser realizada quando não há linhas da fonte de origem que correspondem com a fonte de destino. 
 

WHEN NOT MATCHED BY SOURCE THEN Define a ação a ser realizada quando há linhas na fonte de destino, mas não há linhas correspondentes na fonte de origem.

 

Exemplo

CREATE TABLE #Tabela1 ( Nome VARCHAR (100)

                                       , Cadastro DATETIME

                                       , Alteracao DATETIME

                                       , Ativo BIT)

INSERT #Tabela1

VALUES (‘Andressa’, GETDATE(), NULL, 1),

            (‘Joao’, GETDATE(), NULL, 1),

            (‘Maria’, GETDATE(), NULL, 1),

            (‘Ana’, GETDATE(), NULL, 1)

CREATE TABLE #Tabela2 ( Nome VARCHAR(100)

                                       , Twitter VARCHAR(100))

INSERT #Tabela2

VALUES (‘Andressa’,‘@dre_martins’),

            (‘Marieta’,‘@Marieta’),

            (‘Joaquim’,‘@Joaquim’),

            (‘José’,‘@jose’)

SELECT * FROM #Tabela1

SELECT * FROM #Tabela2

 
 

image

Agora vamos ao comando merge:

 

MERGE #Tabela1 AS Destino

USING #Tabela2 AS Origem

ON Destino.Nome = Origem.Nome

— Há registro no destino e na origem

WHEN MATCHED

THEN UPDATE SET Ativo = 0, Alteracao = GETDATE()

–Quando não há registro no destino e há na origem

WHEN NOT MATCHED

THEN INSERT (Nome, Cadastro, Ativo) VALUES (Origem.Nome, Getdate(),1)

–Quando  há registro no destino mas não há na origem

–e que sejam diferente de João

 

WHEN NOT MATCHED BY SOURCE AND Nome <>‘Joao’

THEN UPDATE SET Ativo = NULL, Alteracao = GETDATE();

SELECT * FROM #Tabela1

SELECT * FROM #Tabela2

 

Resultado:

image

 

Saiba mais

MERGE (Transact-SQL)

 

[]’s

Andressa Martins

Utilizando o Document Map no Reporting Services

Document map é um conjunto de links de navegação exibidos no relatório, esses links podem ser definidos manualmente, setando a propriedade DocumentMapLabel do objeto ou dinamicamente como no exemplo que vamos criar nesse post.

Crie um projeto e selecione o template Report como no exemplo abaixo:

image

Adicione um Shared DataSource, clique com o botão esquerdo e selecione a opção Add>>New data source, e configure as propriedades da sua conexão.

Agora clique com o botão direito da pasta  Reports, e selecione a opção Add>>New Item.

image

Defina o DataSource a ser utilizado, como esse item já existe no SharedDataSouces iremos apenas referenciá-lo.

Na aba ReportData clique com o botão direto em DataSource e selecione add New Data Source, na janela que aparecerá selecione a opção Use shared data source reference, e escolha o data source criado anteriormente.

image

Agora que a nossa fonte de dados já está definida vamos criar o data set, você pode definir um shared data set e somente referenciar do mesmo jeito que fizemos com o data source ou pode adicionar um novo, nesse exemplo eu adicionei um novo data set.

Defina a fonte de dados e por fim a query ou a stored procedure que irá retornar os dados.

Você pode fazer o download do banco que utilizei para esse exemplo, o link se encontra no final do artigo.

SELECT     mon.pkIdMontadora
    , mon.Montadora
    , mar.pkIdMarca
    , mar.Marca
    , cat.pkIdCategoria
    , cat.Categoria
    , subc.pkIdSubCategoria
    , subc.SubCategoria
    , m.Modelo
FROM     Modelo AS m
    INNER JOIN SubCategoria AS subc ON m.fkIdSubCategoria = subc.pkIdSubCategoria
    INNER JOIN Marca AS mar ON m.fkIdMarca = mar.pkIdMarca
    INNER JOIN Montadora AS mon ON mar.fkIdMontadora = mon.pkIdMontadora
    INNER JOIN Categoria AS cat ON subc.fkIdCategoria = cat.pkIdCategoria

Eu criei uma tabela agrupada por montadora, marca, categoria e subcategoria.

Após isso vamos iniciar a montagem do nosso document map.

Clique com o botão direito no grupo montadora >> group properties…

image

Você irá visualizar uma tela semelhante à tela abaixo. Na guia Advanced selecione o campo que você deseja que seja exibido no document map, no nosso caso é a montadora.

image

Ao clicar em preview, já conseguimos visualizar o primeiro nível do nosso document map.

image

Vamos adicionar mais alguns níveis a esse document map. repetindo a mesma estrutura que foi realizada para a montadora, porém agora com o campo marca.

Ao finalizar as etapas, teremos dois níveis na hierarquia do document map.

image

Repita o processo para todos os níveis desejados, no meu caso tenho os seguintes níveis:

Montadora

Marca

Categoria

SubCategoria

Modelo (detalhe)

Veja como ficou o resultado após todos os níveis finalizados:

image

Conforme dito no inicio do post, você pode adicionar objetos para o documento map alterado a propriedade DocumentMapLabel com o nome que você deseja que seja exibido.

image

Como podem verificar, os retângulos estão sendo listados juntamente com os grupos da tabela.

image

Clique aqui para fazer o download da base e do exemplo.

Até a próxima!

Andressa Martins

Nomenclatura de stored procedure

Como anunciamos por aqui, eu e Andressa realizamos um webcast no fim de fevereiro. No fim, tivemos tempo para responder algumas perguntas ao vivo. E uma delas foi se há perda de performance no uso da nomenclatura SP no início da stored procedure. Eu respondi a pergunta, porém,  algumas pessoas nos procuraram para dizer que desconheciam esta recomendação.  E isso é uma verdade, pois vejo muitas empresas  utilizando essa nomenclatura em seus bancos de dados. Portanto, eu resolvi escrever mais sobre assunto.

Como eu respondi no webcast, a perda de performance pode ser irrisória, dependendo da aplicação.  Entretanto temos outro problema a ser observado, a segurança.  Pois ao executar uma stored procedure iniciada por SP, o SQL Server busca no banco master, o que pode deixar brechas para atos maliciosos.

E o que seria um ato malicioso? Imaginem uma instituição bancária, onde existe uma stored procedure chamada ‘SP_TransferenciaContaCorrente’ no banco de dados MovimentacaoBancaria,  que é executada quando há a transferência entre contas correntes. Até aqui  nenhum problema, não é? Agora imaginem a mesma procedure sendo criada no banco master, entretanto transferindo um centavo para conta de algum espertinho ao executar cada transferência. O que vai acontecer aqui? Quem vai ser executada primeira? Como eu disse no nosso webcast, ao criarmos uma stored procedure iniciando com SP, o SQL Server entende que é uma procedure de sistema e procura primeiro no banco master. Ao encontrá-la, ele executará e obviamente não passará pelo banco MovimentacaoBancaria.  Então o espertinho receberá um centavo a cada transferência efetuada.

Portanto, baseado na explicação acima, a melhor prática não recomenda o uso da nomenclatura ‘SP’ no ínicio da stored procedure por deixar furos de segurança na sua instância do SQL Server e de desempenho dependendo da aplicação. Recomenda-se utilizar proc_nomedaprocedure, USP_nomedaprocedure ou qualquer nome desejado. Além disso, a partir do SQL Server 2008, é possível utilizar a ferramenta Policy Management para definir padrões de nomenclatura (assunto para outro post!), evitando nomes indesejados como o explicado nesse post.

Como importar um ShapeFile para um banco de dados SQL Server

O que é um ShapeFile?

É um vetor digital que contem geoinformações, geralmente representado por pontos, linhas e polignos.

Um shapefile é composto principalmente por 3 arquivos:

.SHP Arquivo principal que contém os dados de referencia geográfica.

.SHX São dados de índices que apontam para a estrutura do arquivo .shp, para agilizar a navegação no shapefile.

.DBF Contém os atributos para cada shape, em formato Xbase(DBase).

Onde conseguir?

Na loja virtual do IBGE, o Cd com os dados custa R$ 50,00, no site do GisMaps ou fazendo um busca na internet você consegue, porém nem todos shapefiles são íntegros.

Como importar para o Sql Server 2008?

Nesse exemplo eu vou utilizar a ferramenta SQL Server 2008 Spatial Tools.

 

Faça o download de algum ShapeFile, no exemplo eu utilizei o shapefile das meso regiões

image

 

Abra o programa Shape2Sql.exe, clique em configure e defina as propriedades de conexão.

 

image

 

Escolha o diretorio onde está armazenado o ShapeFile, escolha o tipo de que contém no shape (geometry ou geography), caso deseje altere a nomenclatura da tabela, ou os campos que serão carregados no banco de dados, após essa etapa clique em “Upload to Database”

 

image

 

ShapeFile carregado com Sucesso! Veja o resultado:

 

image

 

 

Até a próxima!

Dre

CheckList diário utilizando o Reporting Services

Há algum tempo atrás eu li um artigo do Fabricio Lima (Criando um CheckList Automático do Banco de Dados ), adaptei os scripts dele as minhas necessidades e montei um relatório no Reporting Services, segue passo a passo o relatório que recebo diariamente por e-mail.

Para desenvolver um relatório é necessário ter instalado o Business Intelligence Development Studio, caso já tenha crie um projeto do tipo “Report Server Project”

clip_image002

Agora precisamos das fontes de dados compartilhadas, na aba Solution explorer clique com o botão direito em cima da pasta “Shared Data Sources” e selecione a opção “Add New Data Source”.

clip_image003

Defina as propriedades da conexão com o banco de dados.

clip_image005

Agora é necessário criar os DataSet de acordo com a sua necessidades, segue abaixo alguns

exemplos:

Para obtermos o tamanho das bases de dados da instância utilizamos a procedure abaixo.

— ============================================= 
— Author:  <Andressa> 
— Create date: <18/02/2011> 
— Description: <retorna dados do espaço utilizado pelas bases> 
— ============================================= 
CREATE PROCEDURE [dbo].[usp_Relatório_DBCheckList] 
  
AS 
BEGIN  
DECLARE @tabela TABLE (Nome  NVARCHAR(128),Tamanho float,NaoAlocado float) 
DECLARE AllDatabases CURSOR FOR  
  SELECT name
  FROM sys.databases
  WHERE STATE = 0 
   
OPEN AllDatabases 
 
   DECLARE @DBNome NVARCHAR(128),@SQL NVARCHAR(MAX) 
 
    FETCH NEXT FROM AllDatabases INTO @DBNome 
     WHILE (@@FETCH_STATUS = 0) 
      BEGIN 
        SET @SQL = N’USE ‘ + @DBNome +’ 
         declare @objname nvarchar(776), @id int   ,@type character(2)  ,@pages bigint    , 
         @dbname sysname    ,@dbsize bigint    ,@logsize bigint    ,@reservedpages  bigint    , 
         @usedpages  bigint    ,@rowCount bigint   
          
         SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)   
         if @id is null   
          begin   
            select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))   
             ,@logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))   
            from dbo.sysfiles   
          end 
          select   @reservedpages = sum(a.total_pages),   
             @usedpages = sum(a.used_pages),   
             @pages = sum(   
            CASE   
             When it.internal_type IN (202,204) Then 0   
             When a.type <> 1 Then a.used_pages   
             When p.index_id < 2 Then a.data_pages   
             Else 0   
            END   
            )   
         from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id   
           left join sys.internal_tables it on p.object_id = it.object_id   
           select database_name = db_name(),   
           database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))    
           * 8192 / 1048576,15,2)),   
           ”unallocated space” = ltrim(str((case when @dbsize >= @reservedpages then   
           (convert (dec (15,2),@dbsize) – convert (dec (15,2),@reservedpages))    
           * 8192 / 1048576 else 0 end),15,2) ) ‘  
        INSERT INTO @tabela 
        EXEC sp_executesql @SQL 
 
         FETCH NEXT FROM AllDatabases INTO @DBNome 
      END 
    SELECT Nome, Tamanho, NaoAlocado from @tabela 
  CLOSE AllDatabases 
  DEALLOCATE AllDatabases 
END

Agora referencie a procedure no dataset.

clip_image007

Tamanho das unidades de Disco do servidor (DataSet_Disco)

 
XP_FIXEDDRIVES
 

 

Tamanho dos arquivos de log de toda a instância (DataSet_LogSpace)

DBCC SQLPERF(LOGSPACE)
 

 

 

Listagem de Jobs que estão em execução (DataSet_JobsExecucao)

SELECT Name AS Nome

,run_Requested_Date AS DataExecucao

,DATEDIFF(mi,run_Requested_Date

,GETDATE()) AS Duracao_Minutos

FROM msdb..sysjobactivity A INNER JOIN

msdb..sysjobs B ON A.job_id = B.job_id

WHERE start_Execution_Date IS NOT NULL AND stop_execution_date IS NULL

Listagem dos Jobs que não foram executados com sucesso (DataSet_JobFailed)

DECLARE @Ontem INT

DECLARE @OntemHorario INT

SET @OntemHorario= CAST((RIGHT(‘0’+CONVERT(VARCHAR(2), (DATEPART(HOUR,DATEADD(HOUR,-1, GETDATE())))), 2) +RIGHT(‘0’+CONVERT(VARCHAR(2), (DATEPART(MINUTE,GETDATE()))), 2)+RIGHT(‘0’+CONVERT(VARCHAR(2), (DATEPART(SECOND,GETDATE()))), 2)) AS INT)

SET @Ontem = CAST(CONVERT (VARCHAR(8),(DATEADD (DAY, -1, GETDATE())),112) AS INT)

CREATE TABLE #HistoricoJobs( Cod INT identity(1,1)

,IDInstancia INT

,IDJob VARCHAR(255)

,NomeJob VARCHAR(255)

,IDStep INT

,NomeStep VARCHAR(255)

,IDSqlMensagem INT

,SqlSeverity INT

,SqlMensangem VARCHAR(3990)

,IDStatusExecucao INT

,DataExecucao VARCHAR(20)

,HorarioExecucao VARCHAR(20)

,DuracaoExecucao INT

,EmailOperador VARCHAR(100)

,NetSenderOperador VARCHAR(100)

,PagerOperador VARCHAR(100)

,TentativasRealizadAS INT

,NomeServidor VARCHAR(100))

INSERT INTO #HistoricoJobs

EXEC Msdb.dbo.SP_HELP_JOBHISTORY NULL,NULL,NULL,NULL,NULL,@Ontem,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,’FULL’

SELECT NomeJob

,CASE

WHEN IDStatusExecucao = 0 THEN ‘Failed’

WHEN IDStatusExecucao = 1 THEN ‘Succeeded’

WHEN IDStatusExecucao = 2 THEN ‘Retry (step only)’

WHEN IDStatusExecucao = 3 THEN ‘Canceled’

WHEN IDStatusExecucao = 4 THEN ‘In-progress message’

WHEN IDStatusExecucao = 5 THEN ‘Unknown’ END Status

,CAST(DataExecucao + ‘ ‘ + RIGHT(’00’ + SUBSTRING(HorarioExecucao,(LEN(HorarioExecucao)-5),2) ,2)+ ‘:’ +

RIGHT(’00’ + SUBSTRING(HorarioExecucao,(LEN(HorarioExecucao)-3),2) ,2)+ ‘:’ +

RIGHT(’00’ + SUBSTRING(HorarioExecucao,(LEN(HorarioExecucao)-1),2) ,2) AS VARCHAR) Dt_Execucao

,RIGHT(’00’ + SUBSTRING(cast(DuracaoExecucao AS VARCHAR),(LEN(DuracaoExecucao)-5),2) ,2)+ ‘:’ +

RIGHT(’00’ + SUBSTRING(cast(DuracaoExecucao AS VARCHAR),(LEN(DuracaoExecucao)-3),2) ,2)+ ‘:’ +

RIGHT(’00’ + SUBSTRING(cast(DuracaoExecucao AS VARCHAR),(LEN(DuracaoExecucao)-1),2) ,2) DuracaoExecucao

,SqlMensangem

FROM #HistoricoJobs

WHERE ((DataExecucao >=@Ontem AND HorarioExecucao >= @OntemHorario) OR DataExecucao >@Ontem)

AND IDStep = 0

AND IDStatusExecucao <> 1

ORDER BY Dt_Execucao

DROP TABLE #HistoricoJobs

Listagem dos últimos backups (DataSet_Backup)

SELECT database_name as NomeDataBase

, name as Nome

,Backup_start_date as DataInicio

, datediff(mi,Backup_start_date,Backup_finish_date) Duracao_Min

, server_name as NomeServidor

,recovery_model as RecoveryModel

, cast(backup_size/1024/1024 as numeric(15,2)) As Tamanho

,type as Tipo

FROM msdb.dbo.backupset B

INNER JOIN msdb.dbo.backupmediafamily BF ON

B.media_set_id = BF.media_set_id

WHERE Backup_start_date >= DATEADD(HH, 18 ,@Dt_Referencia – 1 ) –backups realizados a partir das 18h de ontem

and Backup_start_date < DATEADD (DAY, 1, @Dt_Referencia)

order by database_name, Backup_start_date

/

Após criar os DataSets você terá uma lista semelhante a imagem abaixo:

clip_image018

Para criar um relatório clique com o botão direito em cima da pasta report e “New Item”, selecione “report”, nomeie o arquivo e clique em “Add”. Feito isso você precisa  criar os DataSources/DataSets utilizando o DataSource/DataSet reference correspondente.

image

Com a parte de acesso a dados já finalizada, precisamos criar a parte gráfica do relatório, montei umas tabelas simples e sem formatação apenas para o melhor entendimento.

Para adicionar um campo à uma tabela é simples, somente arrastar o campo do dataset para a tabela desejada.

clip_image020

Abaixo eu apliquei uma formatação simples apenas para diferenciar os dados do nosso relatório (job, bkp, log…)

Utilizei um recurso opcional de alterar a cor da célula de acordo com o valor da linha, assim quando um banco de dados estiver com pouco espaço alocado eu posso visualizar de maneira mais rápida facilitando a tomada de decisão, segue o comando que eu montei.

image

=IIF(
((Fields!Tamanho.Value-Fields!NaoAlocado.Value)/Fields!Tamanho.Value)>0.9,
    "#f28989",
    IIF((((Fields!Tamanho.Value-Fields!NaoAlocado.Value)/Fields!Tamanho.Value)>0.8
            and ((Fields!Tamanho.Value-Fields!NaoAlocado.Value)/Fields!Tamanho.Value) < 0.9),
    "#ffff80",
    "#c0ffc0"))

Você pode adaptar a expressão acima para toda as tabelas.

Interpretação  do código:

Quando o espaço alocado do meu banco de dados for…

  • maior que 90% a linha fica vermelha.
  • maior que 80% e menor que 90% a linha fica amarela.
  • menor que 80% a linha fica verde.

Vejam como ficou o relatório depois de renderizado.

image



 
O projeto de relatórios está disponível pra download.
 
Espero que tenham gostado da dica.
 
Andressa