SQL Server: Transformando Linhas em Colunas

Olá,

Ao desenvolver relatórios existem situações em que é necessário transformar registros (linhas) do banco de dados em uma string única (coluna).

Por exemplo, se no OneBoss você gravar um pedido com pagamento em 3 vezes (10, 25 e 40 dias) os dados de pagamento são gravados em uma tabela auxiliar de vendas, descrevendo cada um dos dias e valor a ser pago em cada dia. Estes dados são gravados em linhas (como no modelo abaixo):

O nosso desafio agora é fazer uma listagem que traga duas colunas: o ID do pedido e os dados de recebimento (mencionados acima). Uma forma bem fácil de fazer isto é usar o comando FOR XML conforme o script abaixo:

SELECT
P.IDPedido,
STUFF((SELECT ‘/ ‘ + CONVERT(VARCHAR, R.Dias)
FROM Recebimento R
WHERE R.IDPedido = P.IDPedido
ORDER BY Dias
FOR XML PATH(”)), 1, 1, ”) FormaPagamento
FROM Pedidos P
GROUP BY P.IDPedido
ORDER BY 1

A saída será algo assim:

ID       FormaPagamento

1          10/25/40

2          30/60/90

 

Simples, não?

Sabemos que é só TI, mas adoramos 😉

SQL Server – Espaço vazio nos bancos de dados

Conforme os servidores vão usando o banco de dados, vão se criando espaços vazios nos mesmos. Com o tempo é interessante que se faça o processo de compactação (ou Shrink) destes bancos de dados, caso você tenha problema de espaço em disco.

Para saber quanto cada banco de dados tem de espaço em branco, disponibilizamos este script abaixo (este pode ser encontrado também pela internet).

Sabemos que é só SQL Server, mas adoramos 😉

if exists (select * from tempdb.sys.all_objects where name like ‘%#dbfreesize%’)
drop table #dbfreesize
create table #dbfreesize
(name sysname,
database_size varchar(50),
Freespace varchar(50)default (0.00))

insert into #dbfreesize(name,database_size,Freespace)
exec sp_msforeachdb
‘use [?];SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ”MB”)
,”unallocated space” = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) – convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + ” MB”)
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (
202
,204
,211
,212
,213
,214
,215
,216
)
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
INNER 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
) AS partitions’

select * from #dbfreesize

SQL Server: Como saber quanto de memória cada banco de dados está usando?

Quando você tem um servidor com muita memória sobrando, é fácil deixar com que o SQL Server use toda a memória para alocar cache para acesso mais rápido aos dados, mas caso seus banco de dados comecem a usar toda a memória do servidor e você queira gerenciar este uso, o SCRIPT abaixo vai te ajudar.

Este SCRIPT, muito útil por sinal, mostra quando cada banco de dados do servidor está usando de memória.

;WITH src AS

(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN ‘Resource DB’
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128
FROM src
ORDER BY db_buffer_MB DESC;

Sabemos que é só SQL Server, mas adoramos 😉

Equipe Mizusoft.

SQL Server 2008+ – Permitindo alterar estrutura de tabela

Até o SQL Server 2005 podíamos modificar a estrutura de uma tabela sem problema algum, mas isso no SQL Server 2008 não é mais permitido (será?).

Pois bem, existe uma configuração no SQL Server 2008 (desde a primeira versão e serve para o SQL Server 2012 também) que permite que as tabelas sejam alteradas, conforme imagem no final.

É só desmarcar a opção em destaque e o SQL Server não vai barrar mais a alteração.

Explicando um pouco por que o problema ocorre e o que a Microsoft buscou diminuir o risco: Quando se faz uma alteração em uma tabela o SQL faz o seguinte:

1. Cria uma nova tabela temporária;
2. Copia os dados da tabela sendo alterada para a nova tabela temporária;
3. Faz um DROP na tabela sendo alterada;
4. Cria uma nova tabela com a nova estrutura;
5. Copia os dados da tabela temporária para a nova tabela;
6. Faz um DROP da tabela temporária.

O problema é que em uma etapa destas, pode dar algum problema e termos perda de dados.

Nós sabemos, é só SQL Server… Mas adoramos!!

 

Figura 1

Windows 8 – Resolvendo problema de resolução

No Windows 8 a Microsoft tenta deixar a interface com melhor usabilidade e para isso aumentou o padrão de resolução assim como mostra a figura abaixo:

 

O problema é que para o One Boss isto distorce a tela e a visualização da mesma fica ruim.

Para resolver o problema,

1. Vá no ambiente desktop (Área de trabalho) ou conhecido como o ambiente antigo do Windows;
2. Clique com o botão direito do Mouse em uma área vazia e clique em Screen Resolution (Resolução de tela);
3. Clique em Make text and Other Items Larger or Smaller (Fazer texto e outros itens menores ou maiores)
4. A tela acima se abrirá, clique em 100% e pressione o botão OK.

é… sabemos que é só TI, mas adoramos!!

Instalação – Invalid Drive

Ao instalar um aplicativo que usa como instalador o InstallShield, você pode se deparar com o seguinte problema: Invalid Drive X: (X corresponde a letra do drive reclamada pelo sistema operacional.

O problema ocorre por que em algum momento você perdeu o mapeamento do drive onde o InstallShield quer jogar uns arquivos de configuração. Esse drive não existe mais.

Para resolver o problema siga as dicas abaixo (Importante, tenha habilidade e saiba que vamos mexer no registro do Windows):

1. Pressione WINDOWS + R
2. Digite regedit e pressione ENTER.
3. No registro do windows, localize a seguinte chave: HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders
4. No painel a direita, note os valores em cada coluna. Se um deles contiver o drive que não está correto no seu computador, clique com o botão direito do mouse e selecione Modify/Modificar.
5. Coloque o drive/caminho correto (Ex: C:\MeuDrive) para as novas configurações.
6. Repita isso para as chaves a seguir:

• HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders

Feche o registro do windows e rode o instalador novamente.

Eu sei… é só TI, mas adoramos! 😉

Saber os certificados digitais instalados no computador

Para saber os certificados digitais instalados no seu computador:

Digite o seguinte comando no menu iniciar: mmc \windows\system32\certmgr.msc

Essa e outras dicas podem ser vistas no nosso twitter: https://twitter.com/mizusoft_biz

Dica

Quer saber mais sobre certificado digital?

http://pt.wikipedia.org/wiki/Certificado_digital

Essa e outras dicas podem ser vistas no nosso twitter: https://twitter.com/mizusoft_biz

Cursor

Um certo mito é em cima de um recurso simples mas muito útil nos bancos de dados com SQL Server. O Cursor permite que o DBA percorra uma tabela virtual e execute comandos como um FOR EACH em C#. Abaixo um exemplo simples de uso.

Esse script percorre nossa tabela de produtos e faz exclusão nas tabelas de estoque para produtos que tenham sido movimentados sem SKU, deixando o estoque do SKU somente. Rolo é um identificador unico para o nosso estoque, para industria têxtil.

SET NOCOUNT ON;

DECLARE @CodigoProduto varchar(36)
DECLARE @QuantidadeTotalLinhasExcluida int

——– Lista os produtos ——–

DECLARE produtosCursor CURSOR FOR
SELECT ProductID
FROM ProdutcsTable
WHERE ManufactProduct = 0;

OPEN produtosCursor

FETCH NEXT FROM produtosCursor
INTO @CodigoProduto

SET @QuantidadeTotalLinhasExcluida = 0

WHILE @@FETCH_STATUS = 0
BEGIN

— Verifica se o produto tem movimento de estoque com rolo

DECLARE @QuantidadeEstoqueRolo int

SET @QuantidadeEstoqueRolo = ( SELECT COUNT(*) FROM StockTable WHERE FK_ProdutoId = @CodigoProduto AND @CodigoRolo <> ” )

IF (@QuantidadeEstoqueRolo > 0)
BEGIN

SET @QuantidadeTotalLinhasExcluida = @QuantidadeTotalLinhasExcluida + @QuantidadeEstoqueRolo

PRINT @CodigoProduto + ‘ >> ‘ + CAST(@QuantidadeEstoqueRolo AS VARCHAR);

DELETE FROM StockTableByDay WHERE FK_ProductId = @CodigoProduto AND @CodigoRolo = ”
DELETE FROM StockTable WHERE FK_ProductId = @CodigoProduto AND @CodigoRolo = ”

END

FETCH NEXT FROM produtosCursor
INTO @CodigoProduto
END

PRINT CAST(@QuantidadeTotalLinhasExcluida AS VARCHAR) + ‘ Linhas’;

CLOSE produtosCursor;
DEALLOCATE produtosCursor;