Posts Tagged ‘sql server’

Pegar tamanho das bases no sql server

agosto 15th, 2016
EXEC sp_MSforeachdb 'use ? exec sp_spaceused'

sp_spaceused é o cara que ve as informações de cada base, mas se rodar só ele, vai aparecer as informações para a base que está no contexto, o sp_MSforeachdb faz um foreach nas bases e vai rodando o sp_spaceused para cada uma, o “use ?” vai alterando as bases que o sp_MSforeachdb buscou.

Redirecionamento de IP no Windows

agosto 5th, 2016

Precisei redirecionar tudo que chegava para um IP específico no servidor em uma porta específica para um destino e porta específica. Consegui fazer com o comando abaixo

netsh interface portproxy add v4tov4 listenport=1433 listenaddress=192.168.0.1 connectport=1433 connectaddress=192.168.1.1 protocol=tcp

O comando acima adiciona uma configuração de ipv4 para ipv4 para todos os pacotes TCP que chegarem no IP 192.168.0.1 na porta 1433, e manda tudo para o IP 192.168.1.1 na porta 1433.
Lembrando que para a regra funcionar, é necessário que nenhum serviço esteja executando a porta que está no listenport, no meu caso, precisei parar o serviço do SQL Server, pois ele usa a porta 1433 como padrão.

Removendo linhas duplicadas no sql server

março 22nd, 2016

Tive um problema que foi assim:
tabela dados

ID DATAINICIO
A001 2011-01-01
A001 2011-01-01
A002 2011-01-01
A002 2011-01-01
A002 2011-01-01
A003 2011-01-01

Tinha várias tabelas com várias linhas duplicadas, como eram muitas, era inviável remover uma a uma na mão. Utilizei o script abaixo para remover os campos duplicados.

WITH cte AS (
  SELECT [%CAMPO1%], [%CAMPO2%], 
     row_number() OVER(PARTITION BY %CAMPO1%, %CAMPO2% ORDER BY %CAMPO1%) AS [rn]
  FROM [%TABELA%]
)
DELETE cte WHERE [rn] > 1

 

No caso em questão, fica

WITH cte AS (
  SELECT [ID], [DATAINICIO], 
     row_number() OVER(PARTITION BY ID, DATAINICIO ORDER BY ID) AS [rn]
  FROM [dados]
)
DELETE cte WHERE [rn] > 1

 

Não precisa informar todos os campos da tabela, no exemplo foi colocado dois somente para ter certeza que mais de um campo tem o mesmo valor, é para ter certeza que a linha toda está realmente duplicada, é possível colocar mais campos também.

Links que me ajudaram:
http://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server
http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server

Limpar banco tempdb do sql server

março 15th, 2016

O banco tempdb estava consumindo um espaço absurdo do sql server. Pesquisando achei a seguinte solução.

-- write everything from your buffers to the disc!
CHECKPOINT; 
GO
-- Clean all buffers and caches
DBCC DROPCLEANBUFFERS; 
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE('ALL');
DBCC FREESESSIONCACHE;
GO
-- Clean tempdb
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY)
GO

SQL Server pulando campo com id auto increment

outubro 22nd, 2015

Isso acontece a partir da versão 2012 do sql server. Quando tu tem uma tabela com campo id auto increment, e o serviço do sql server é parado de forma abrupta, após a inicialização do serviço, a próxima inserção na tabela terá um acréscimo de 1000, para o tipo int, ou 10000, para o tipo bigint no campo id.

FullTable

Na internet encontrei alguns links para o contorno desse problema, um deles é criar uma sequência, conforme descrito no link http://www.codeproject.com/Tips/668042/SQL-Server-Auto-Identity-Column-Value-Jump-Is, e a outra é adicionar o parametro -t272, na inicialização do sql server.

StartupParameter

Pesquisando vi que esse parâmetro não é oficialmente documentado pela Microsoft, e ele parece modificar a engine do sql server. Vi que muita gente fez isso para sanar o problema e não teve problemas com essa configuração, mas não há nenhuma documentação oficial a respeito disso.

Referência:
http://www.codeproject.com/Tips/668042/SQL-Server-Auto-Identity-Column-Value-Jump-Is
http://dba.stackexchange.com/questions/88930/identity-value-jumps-when-restarting-sql-server

mssql_connect no php 5.3

maio 16th, 2013

A partir do php 5.3 VC9, a dll do conector do mssql do php não vem junto no diretório de extensões do php, então se tu descomentar a dll no php.ini ela não irá funcionar. O substituto para o mssql_connect é o sqlsrv_connect, que pode ser baixado no link http://www.microsoft.com/en-us/download/details.aspx?id=20098 , após baixar, é necessário instalar o sql native client em sua máquina, o download pode ser feito em http://go.microsoft.com/fwlink/?LinkID=188401&clcid=0x409 para versões x64. Se instalar a versão 3.0 do sqlsrv_connect, o sql native client deve ser na versão 2012, que pode ser baixado em http://go.microsoft.com/fwlink/?LinkID=236805. Nesse outro link tem mais informações referente às versões e requeirimentos para o funcionamento do sqlsrv_connect http://msdn.microsoft.com/en-us/library/cc296170.aspx

E exemplos de funcionamento tem no php.net

Alterar schema de uma tabela do sql server

março 8th, 2013
 ALTER SCHEMA newSchema TRANSFER oldSchema.table

Pegar todas as tabelas e colunas de uma base sql server

fevereiro 28th, 2013

Pegar todas as tabelas de uma base

USE NomeDaBase
GO 
SELECT *
FROM sys.Tables
GO

Substitua NomeDaBase pela sua base.

Pegar todas as colunas de uma tabela

SELECT COLUMN_NAME,* 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NomeDaTabela'

Substitua NomeDaTabela pela sua tabela.

Converter data/hora na query do sql server

junho 28th, 2012

Alguns exemplos de conversão de hora do mssql.

-- SQL Server string to date / datetime conversion - datetime string format sql server
-- MSSQL string to datetime conversion - convert char to date - convert varchar to date
-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)
SELECT CONVERT(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)
SELECT CONVERT(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000
 
-- Without century (yy) string date conversion - convert string to datetime function
SELECT CONVERT(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM)
SELECT CONVERT(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000
 
-- Convert string to datetime sql - convert string to date sql - sql dates format
-- T-SQL convert string to datetime - SQL Server convert string to date
SELECT CONVERT(datetime, '10/23/2016', 101) -- mm/dd/yyyy
SELECT CONVERT(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with century
SELECT CONVERT(datetime, '23/10/2016', 103) -- dd/mm/yyyy
SELECT CONVERT(datetime, '23.10.2016', 104) -- dd.mm.yyyy
SELECT CONVERT(datetime, '23-10-2016', 105) -- dd-mm-yyyy
-- mon types are nondeterministic conversions, dependent on language setting
SELECT CONVERT(datetime, '23 OCT 2016', 106) -- dd mon yyyy
SELECT CONVERT(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy
-- 2016-10-23 00:00:00.000
SELECT CONVERT(datetime, '20:10:44', 108) -- hh:mm:ss
-- 1900-01-01 20:10:44.000
 
-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format
SELECT CONVERT(datetime, 'Oct 23 2016 11:02:44:013AM', 109)
-- 2016-10-23 11:02:44.013
SELECT CONVERT(datetime, '10-23-2016', 110) -- mm-dd-yyyy
SELECT CONVERT(datetime, '2016/10/23', 111) -- yyyy/mm/dd
-- YYYYMMDD ISO date format works at any language setting - international standard
SELECT CONVERT(datetime, '20161023')
SELECT CONVERT(datetime, '20161023', 112) -- ISO yyyymmdd
-- 2016-10-23 00:00:00.000
SELECT CONVERT(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm
-- 2016-10-23 11:02:07.577
SELECT CONVERT(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)
-- 1900-01-01 20:10:25.300
SELECT CONVERT(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)
-- 2016-10-23 20:44:11.000
SELECT CONVERT(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm
-- 2016-10-23 20:44:11.500
 
-- Style 126 is ISO 8601 format: international standard - works with any language setting
SELECT CONVERT(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)
-- 2008-10-23 18:52:47.513
SELECT CONVERT(datetime, N'23 ???? 1429  6:52:47:513PM', 130) -- Islamic/Hijri date
SELECT CONVERT(datetime, '23/10/1429  6:52:47:513PM',    131) -- Islamic/Hijri date
 
-- Convert DDMMYYYY format to datetime - sql server to date / datetime
SELECT CONVERT(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)
-- 2016-01-31 00:00:00.000
-- SQL Server T-SQL string to datetime conversion without century - some exceptions
-- nondeterministic means language setting dependent such as Mar/Mär/mars/márc
SELECT CONVERT(datetime, 'Oct 23 16 11:02:44AM') -- Default
SELECT CONVERT(datetime, '10/23/16', 1) -- mm/dd/yy U.S.
SELECT CONVERT(datetime, '16.10.23', 2) -- yy.mm.dd ANSI
SELECT CONVERT(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR
SELECT CONVERT(datetime, '23.10.16', 4) -- dd.mm.yy German
SELECT CONVERT(datetime, '23-10-16', 5) -- dd-mm-yy Italian
SELECT CONVERT(datetime, '23 OCT 16', 6) -- dd mon yy non-det.
SELECT CONVERT(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det.
SELECT CONVERT(datetime, '20:10:44', 8) -- hh:mm:ss
SELECT CONVERT(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec
SELECT CONVERT(datetime, '10-23-16', 10) -- mm-dd-yy U.S.
SELECT CONVERT(datetime, '16/10/23', 11) -- yy/mm/dd Japan
SELECT CONVERT(datetime, '161023', 12) -- yymmdd ISO
SELECT CONVERT(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt
SELECT CONVERT(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h)
SELECT CONVERT(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can.
SELECT CONVERT(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC

Fonte: http://www.sqlusa.com/bestpractices/datetimeconversion/

SQL Server listando todas as bases para um usuário

abril 7th, 2012

Por padrão, a instalação do sql server lista todas as bases para um determinado usuário, por exemplo, tu tem um servidor compartilhado e um usuário faz conexão na base dele, por padrão ele conseguirá ver todas as bases que estão no servidor, irá acessar só a base do usuário que fez login, mas terá a listagem de todas as bases. Para não permitir isso, rode o sql abaixo

 REVOKE VIEW ANY DATABASE FROM public;

Para desfazer essa configuração e voltar para o default, rode o sql abaixo:

 GRANT VIEW ANY DATABASE TO public;