Arquivo da categoria ‘T-SQL’

Ae galera tô meio longe do blog há algum tempo mas agora as coisas estão menos "pesadas" e vou tentar atualizá-lo com mais freqüência. Uma dica rápida é a execução de SQL Dinâmico com mais de 4000 caracteres no SQL Server 2000.

Em alguns momentos como geração de scripts ou outras tarefas administrativas nós precisamos elaborar scripts que ocupam mais de 4 mil caracteres… No entanto, no SQL Server 2000 a procedure sp_executesql suporta apenas nvarchar(4000) no máximo. Como o 2000 ainda não possuia o tipo de dados varchar(max), o uso da procedure citada para scripts grandes não é possível.

No entanto, podemos usar a EXEC() concatenando variáveis. Vejam abaixo que estou executando o comando com duas variáveis de 8000 cada:

DECLARE @CMD1 VARCHAR(8000)
DECLARE @CMD2 VARCHAR(8000)

SET @CMD1 = ‘PRINT ”’
SET @CMD2 = REPLICATE(‘A’, 7999) + ””

EXEC(@CMD1 + @CMD2)

Post curto mas útil em muitos casos.

Abraço e até a próxima.

Demétrio Silva

Ae galera sei que esse tópico podem gerar perguntas do tipo: Pra quê?…, Não vejo necessidade disso…, A própria Microsoft recomenda não realizar isso… Enfim, realmente a MS não recomenda nem dá suporte para atualizações de tabelas de sistema. Ela até mesmo bloqueou por padrão a alteração das mesmas.

No SQL Server 2000 o seguinte script permitia realizar alterações nas system tables:

 

–Altera a opção que permite atualizar tabelas de sistema
use master
GO
sp_configure ‘allow updates’,1
GO
reconfigure with override

–cria uma tabela
create table teste ( id int )

–Verifica a tabela criada

select * from sys.sysschobjs where name = ‘teste’

--atualiza a tabela de sistema
update sys.sysschobjs set name = 'teste_new' where name = 'teste'
 
No entanto, a partir do SQL Server 2005 a execução do código abaixo retorna um erro:

 

–atualiza a tabela de sistema
update sys.sysschobjs set name = ‘teste_new’ where name = ‘teste’

Msg 259, Level 16, State 1, Line 2
Ad hoc updates to system catalogs are not allowed.

 

Então como realizar as alterações nas nas versões do SQL Server posteriores ao 2000 ? Podemos “subir” o SQL em modo single user e conectar via DAC. Isso nos permite alterar tabelas de sistema.

Vejamos abaixo:

 

Precisamos primeiramente parar o serviço do SQL Server ( via linha de comando ou mesmo pelo Sql Server Configuration Manager )

Como o nome do meu serviço é MSSQLSERVER, o comando que devo executar é:

net stop MSSQLSERVER

 

E agora devemos localizar onde está a pasta BIN da instância que desejamos realizar as alterações, no meu caso é o seguinte:

 

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn

 

Feito isso, vamos ao prompt de comando e executamos os seguintes comandos:

c:

cd\

cd C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn

Inicia  o SQL Server em single_user. No meu caso, como é uma instância padrão, não preciso informar a instância. Caso fosse uma instância nomeada com o nome de PROD deveríamos especificar o parâmetro –sPROD no comando abaixo:

sqlservr.exe –m

 

Agora se conecte ao SQL Server via DAC ( mais informações sobre acesso via DAC em http://msdn.microsoft.com/en-us/library/ms189595.aspx )

 

Instância padrão e autenticação Windows

sqlcmd –E –A

 

E agora execute o comando desejado:

 

update sys.sysschobjs set name = ‘teste_new’ where name = ‘teste’

GO

 

Note que o SQL Server alterou a tabela de sistema, mesmo sendo uma instância do 2008 no meu caso.

 

Daí no começo falei que muitas pessoas acham isso inútil e uma péssima prática. Sim realmente isso não deve ser usado normalmente mas em alguns casos é essencial.

Exemplo:

 

Imagine que você tenha um ambiente com replicação merge com um publisher e um subscriber ( ambos em SQL 7 ). E que você deseja migrar estes para SQL Server 2005. Daí você realiza um backup e faz um restore na instância 2005. No entanto você ainda não removeu a replicação de ambas as bases. Ao tentar executar a SP

 

sp_removedbreplication ‘nomeDaBase’

 

O SQl Server 2005 retornará inúmeros erros e não consegue remover a replicação. Como contornar ? Updates em tabelas de sistema:

 

UPDATE sysobjects set replinfo=0 WHERE replinfo<>0

 

Precisamos sempre saber o que podemos e devemos fazer e não apenas o que devemos…

 

Abraços galera.

Galera,

 

Hoje vou falar sobre um
assunto bem interessante: Situações onde o bloco catch não é executado. Essa
semana eu estava lendo sobre este assunto e achei útil mostrar em que situações
o bloco catch não é executado.

 

Abaixo temos alguns exemplos:

 

KILL ou Timeout do comando

 

Imagine ter um código onde
você realiza vários updates em uma ou várias tabelas dentro de um bloco begin
try e, caso exista algum problema durante a atualização, a transação deve ser
revertida e a linha e mensagem de erro devem ser logadas em uma tabela
destinada ao log de erros.

 

Bom, vamos criar nossa base de dados e as tabelas conforme
script abaixo:

–CRIA E COLOCA EM USO O DATABASE

CREATE DATABASE
DB_TESTE

GO

 

USE DB_TESTE

GO

 

–CRIA TABELA QUE VAI ARMAZENAR OS ERROS OCORRIDOS

CREATE TABLE
TB_ERROR(ELINE INT
, 
EMESSAGE nvarchar(2048))

 

–CRIA A TABELA PARA TESTAR O UPDATE

CREATE TABLE
CLIENTE ( ID INT, NOME VARCHAR(10), STATUS CHAR(1))

GO

 

–ADICIONA UMA CHECK PARA O CAMPO STATUS ( A = ATIVO, I =
INATIVO )

ALTER TABLE
CLIENTE  WITH
CHECK ADD  CONSTRAINT
CK_STATUS CHECK (
STATUS = ‘A’ OR STATUS = ‘I’)

GO

 

–POPULA A TABELA

INSERT INTO
CLIENTE( ID,
NOME, STATUS ) VALUES ( 1, ‘DEMÉTRIO’, ‘A’ )

INSERT INTO
CLIENTE( ID,
NOME, STATUS ) VALUES ( 2, ‘MARIA’, ‘A’ )

INSERT INTO
CLIENTE( ID,
NOME, STATUS ) VALUES ( 3, ‘JOSÉ’, ‘A’ )

INSERT INTO
CLIENTE( ID,
NOME, STATUS ) VALUES ( 4, ‘JOÃO’, ‘I’ )

INSERT INTO
CLIENTE( ID,
NOME, STATUS ) VALUES ( 5, ‘PEDRO’, ‘I’ )

 

–VERIFICA OS DADOS INSERIDOS

SELECT * FROM CLIENTE

Bom, conforme
podemos verificar, o script abaixo realiza dois updates na tabela, sendo que o
segundo gera um erro. Ao capturar o erro, o bloco catch realiza o rollback da
transação e insere a linha e mensagem de erro na tabela TB_ERROR.

BEGIN TRY

     

      BEGIN TRAN

           

            –ATUALIZA
TODOS OS DADOS DO CAMPO NOME DA TABELA

            UPDATE
CLIENTE SET NOME +=
‘_’

           

            –ATUALIZA
TODOS OS STATUS PARA ‘L’, VAI GERAR ERRO POR CAUSA DA CHECK CONSTRAINT

            UPDATE CLIENTE SET STATUS = ‘L’

     

      COMMIT

     

END TRY

BEGIN CATCH

 

      –SE HOUVE ERRO,
REVERTE A TRANSAÇÃO E LOGA O ERRO NA TABELA

      IF @@TRANCOUNT > 0

            ROLLBACK

 

      INSERT INTO TB_ERROR ( ELINE, EMESSAGE )

      SELECT

            ERROR_LINE(),

            ERROR_MESSAGE()        

           

END CATCH

 

 

Como podemos
verificar no select abaixo, o primeiro update é revertido e um registro é gravado
na tabela de log.

SELECT *
FROM CLIENTE

SELECT * FROM TB_ERROR

–LIMPA O LOG DE ERROS

DELETE TB_ERROR

Até agora sem problemas. Mas, e se alguém executar um kill ou
mesmo existir um timeout na conexão que está executando o update?

A transação é revertida, não através do rollback contido no
bloco catch, mas sim porque esse é procedimento executado quando utilizamos um
kill contra algum processo. No entanto, note que o insert na tabela de log não
é executado, ou seja, isso é uma prova que o bloco catch não foi executado.

Abra duas conexões no SSMS e execute o código abaixo:

BEGIN TRY

     

      BEGIN TRAN

           

            –ATUALIZA
TODOS OS DADOS DO CAMPO NOME DA TABELA

            UPDATE CLIENTE SET NOME += ‘_’

           

–AGUARDA 5 MINUTOS
PARA DAR TEMPO DE REALIZAR O KILL

            WAITFOR
DELAY ’00:05:00′

           

            –ATUALIZA
TODOS OS STATUS PARA ‘L’, VAI GERAR POR CAUSA DA CHECK CONSTRAINT

            UPDATE CLIENTE SET STATUS = ‘L’

     

      COMMIT

     

END TRY

BEGIN CATCH

 

      –SE HOUVE ERRO,
REVERTE A TRANSAÇÃO E LOGA O ERRO NA TABELA

      IF @@TRANCOUNT > 0

            ROLLBACK

 

      INSERT INTO TB_ERROR ( ELINE, EMESSAGE )

      SELECT

            ERROR_LINE(),

            ERROR_MESSAGE()        

           

END CATCH

Verifique o spid da
conexão que está o script acima e na outra janela do SSMS execute o comando
KILL passando o spid da conexão acima.

Após matar o
processo acima, volte na janela que encontra-se o script acima e veja que o SQL
Server gerou o seguinte erro.

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the
current command.  The results, if any,
should be discarded.

Msg
0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Agora, verifique que
a transação foi revertida mas que nenhuma linha foi inserida no log de erros:

SELECT *
FROM CLIENTE

SELECT * FROM TB_ERROR

Esse é um dos casos
onde o catch não é executado, o mesmo acontece quando existe um timeout,
devemos ficar atento quando estivermos utilizando o catch e fazendo algum
tratamento dentro do mesmo.

É isso ai, num próximo post irei mostrar outras situações onde o
bloco catch não é executado.

Abraços

Galera,

As vezes precisamos gerar scripts para alterar o identity de várias tabelas, por exemplo, ambientes replicados onde identitys do publicante e assinante precisam ser diferentes.

Abaixo, mostro um script de como fazer isso:

SET NOCOUNT ON    
GO
        
–declaro uma table que será usada para guardar o nome das tabelas que possuem campos identitys
declare @tabelas table (idx int identity(1,1), tabela varchar(100))             
insert into @tabelas (tabela)                                    
select distinct t.name
from
    sys.tables t
inner join sys.columns c on c.object_id = t.object_id
where
    t.is_ms_shipped = 0   
    and c.is_identity = 1
order by
    name

declare @inicio int, @fim int             
declare @command varchar(1000)

–variável que deve ser setada para o valor do novo identity            
declare @ident_aux int = 15000
             
select @inicio = 1, @fim = max(idx) from @tabelas             

–faço o while ao invés de um cursor e monto o comando
while @inicio <= @fim             
begin       
                                                                 
    select
        @command = ‘dbcc checkident ( ‘ + tabela + ‘, ‘ + ‘reseed’ + ‘, ‘ + cast( @ident_aux as varchar(10) ) + ‘ ) ‘
    from
        @tabelas
    where
        idx = @inicio             
   
    –imprimo o comando para que o mesmo possa ser executado posteriormente
    print( @command )             
    print(‘GO’)
   
    set @inicio = @inicio + 1 
               
end           

Simples não? Mas é muito útil para alteração de identitys em várias tabelas.

Abraços

         

Olá pessoal, hoje vou postar um exemplo bem simples mas que vejo muita dúvida sobre isso nos fóruns e entre amigos.

Como fazer um update usando mais de uma tabela?

Abaixo segue script simples de como fazer:

–Cria as duas tabelas

create table #TB_1 (ID int primary key, NOME varchar(1))

create table #TB_2 (ID int primary key, NOME varchar(1))

 

–Insere alguns dados para teste

insert into #TB_1 (ID, NOME) select 1, ‘A’

insert into #TB_1 (ID, NOME) select 2, ‘B’

insert into #TB_1 (ID, NOME) select 3, ‘C’

 

insert into #TB_2 (ID, NOME) select 1, ‘D’
insert into #TB_2 (ID, NOME) select 2, ‘Z’
insert into #TB_2 (ID, NOME) select 3, ‘A’
 
–verifica os dados inseridos
select * from #TB_1
select * from #TB_2
 
–Atualiza #TB_2 para que todos os registros que se relacionam com #TB_1 fiquem iguais aos valores de #TB_1
update #TB_2
set
    #TB_2.NOME = #TB_1.NOME
from #TB_2
inner join #TB_1 on #TB_2.ID = #TB_1.ID
 
–Mostra que os dados de #TB_1 e #TB_2 agora são idênticos
select * from #TB_1
select * from #TB_2
 

Até a próxima pessoal.

Bom pessoal, hoje vou falar sobre uma dúvida muito recorrente nos fóruns MSDN e comunidades em geral que é restaurar um backup e mudar o local dos arquivos primário e de log.
 
Segue abaixo um exemplo prático:
 

–Primeiro vamos criar um database e colocá-lo em uso

create

database dbTeste

GO

use

dbTeste

GO

 

–verificando onde os arquivos estão alocados

select * from sysfiles

 
–o resultado na minha máquina é:

–C:\Arquivos de programas\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\dbTeste.mdf e
–C:\Arquivos de programas\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\dbTeste_log.LDF
 
–Agora vamos efetuar um backup e dropar o database dbTeste:

backup

database dbTeste to disk = ‘c:\dbTeste.bak’

 

use

master

GO

drop

database dbTeste

GO

 
–Agora sim, vou fazer o restore de nosso database e mudar o local dos arquivos de log e de dados para o c:\

restore database dbTeste

from

disk = ‘c:\dbTeste.bak’

with

    move

    ‘dbTeste’ to ‘c:\dbTeste_data.mdf’,

    move

    ‘dbTeste_log’ to ‘c:\dbTeste_log.ldf’

GO

  

–Verificando onde os arquivos estão alocados:

 

use

dbTeste

GO

 
–verificando novamente onde os arquivos estão alocados

select

* from sysfiles

 

–o resultado na minha máquina agora é:
–c:\dbTeste_data.mdf e
–c:\dbTeste_log.ldf

 
É isso aí pessoal, espero ter ajudado
 
Abraços

Olá Pessoal,

Hojo vou postar um script bem útil para quando precisamos gerar scripts de check constraints de nossas bases de dados. A vantagem da geração via TSQL é a flexibilidade que ela oferece podendo escolher se a constraint será verificada por replicação, gerar para apenas uma tabela ou coluna, dentre outras coisas.

USE

Northwind

GO
 
select

    ‘if object_id(”’+ O.NAME + ”’) is not null begin alter table ‘ + T_OBJ.NAME +

    ‘ drop constraint ‘ + O.NAME + ‘ end ‘ +

    ‘ alter table ‘ + T_OBJ.NAME +

    ‘ add constraint ‘ + O.NAME + ‘ check not for replication ‘ + COM.TEXT

 from

    sysobjects O

inner join syscomments COM on O.ID = COM.ID
inner join sysobjects T_OBJ on O.PARENT_OBJ = T_OBJ.ID
inner join sysconstraints CON on O.ID = CON.CONSTID
inner join syscolumns COL on T_OBJ.ID = COL.ID

    and CON.COLID = COL.COLID

where

    O

.UID = USER_ID()

    and O.XTYPE = ‘C’

 

 

Abraços