Início / dba / Perguntas / 29544
Accepted
Derek Kromm
Asked: 2012-11-30 11:18:59 +0800 CST

Grupo de reversão de instruções DDL

  • 772

Trabalhando no SQL Server 2008 R2, estou tentando reverter um conjunto de instruções DDL como um grupo (pense em um script de atualização para um banco de dados), mas estou tendo problemas.

Pegue o seguinte código:

begin try

begin tran

create table foo (i int)
alter table foo add x dog
insert into foo select 1,1
insert into foo select 1,1,1

commit tran

end try

begin catch

rollback tran
print @@error

end catch

Estou esperando que o try falhe na instrução alter table, vá para o catch, reverta a transação e imprima a mensagem de erro. No entanto, se você verificar seus objetos/tabelas, verá que foo ainda está lá (portanto, a criação da tabela não foi revertida corretamente).

select * from sys.objects where name = 'foo'

O que eu estou fazendo errado aqui?

sql-server sql-server-2008-r2

3 respostas

  1. O erro está acontecendo porque o erro que está sendo lançado faz parte de um erro de recompilação devido à resolução de nome adiada. Olhando para o SQL BOL, eles não ficam presos quando acontecem no mesmo nível que o try...catch. No entanto, se estiver acontecendo em um nível diferente, como SQL dinâmico ou uma chamada de SP, ele será detectado e revertido.

    Usando o Profiler, você pode ver que a instrução "alter table foo add x dog" é recompilada antes de ser executada e, em seguida, apresenta erros e ignora o bloco catch.

    Rastreamento do Profiler com erro

    Se você agrupar as instruções em SQL dinâmico, o erro não será retornado ao Profiler e a transação será revertida

    Rastreamento do Profiler sem erro

    • 5
  2. Best Answer

    O motivo pelo qual você está vendo esse resultado é que o SQL Server não está realmente detectando seu erro ALTER TABLE. Você notará que, ao executar isso, verá a mensagem de erro vermelha em vez de uma linha impressa - você pode verificar isso alterando print @@errorpara algo como print 'HELLO!'; nesse caso, você NÃO verá 'HELLO!' impresso; você verá o erro em seu lugar. Books Online tem uma lista de casos de erros que você não pode detectar .

    Uma alternativa aqui é SET XACT_ABORT ONantes de iniciar suas transações. Em seguida, você pode reverter suas alterações ao receber seu primeiro erro.

    • 4

  3. As coisas são um pouco mais simples, imho. Seu bloco catch não é invocado porque seu código nunca é executado . O SQL Server recebe um bloco de texto:

    blah blah
blah
blah blah
blah

    Em seguida, ele tenta entendê-lo para analisá-lo, o que o transforma em uma árvore sintática, mas ainda não executável. Para torná-lo executável, ele precisa compilá-lo e, nesse estágio, ele falha e uma exceção é lançada. Em nenhum momento a solicitação foi realmente executada, portanto, seu bloco try/catch nem foi iniciado. Este é sempre o caso com erros de sintaxe e compilação versus erros de execução. Você pode enviar a transação e manipulação de exceção para um quadro na pilha, iniciando a transação a partir do cliente (por exemplo, envolvendo-a em um TransactionScope) ou adicionando um quadro ao lote (por exemplo, usando sp_executesql).

    Mas, infelizmente, esta é uma causa perdida. Fazer uma migração de uma transação nunca funcionará na prática. No mínimo, pense em todos os DDL que não suportam transações e pense bem no que acontecerá ao seu log quando você emitir DDL, que é o tamanho dos dados (por exemplo, ALTER TABLE ... ADD COLUMN ... WITH VALUES ).

    Faça um backup, execute sua migração, se ela falhar, restaure a partir do backup. É a única opção viável.

    • 3

relate perguntas