none
Chamando stored procedure dentro de outra stored RRS feed

  • Pergunta

  • Estou executando uma stored que chama outra stored que esta no servidor. Estou usando transação nas 2 stored. Quando meu aplicativo chama a stored recebo a seguinte mensagem:

    O número de transações após EXECUTE indica que uma instrução COMMIT TRANSACTION ou ROLLBACK. Contagem anterior = 1, contagem atual = 0.

    O que fazer, já que as 2 stored precisa de transação?

    quarta-feira, 25 de julho de 2012 12:51

Todas as Respostas

  • Marcelo veja se este link te ajuda:

    http://www.sql-programmers.com/nested-stored-procedures-%E2%80%93-problems-with-transaction-count.aspx

    Vitor Mendes | Seu feedback é muito importante para todos!
    Visite o meu site: http://www.vitormendes.com.br/

    quarta-feira, 25 de julho de 2012 12:56
  • Posso usar somente uma transação. Ela irá funcionar para a 1 e 2 stored?

    quarta-feira, 25 de julho de 2012 14:01
  • Marcelo,

    O que está acontecendo é que a sua procedure interna (que é chamada de dentro de outra) está abrindo uma transação e não está fechando-a. O SQL Server é inteligente e te avisa disso, pra você ficar ligado.

    O correto é você ver se ele tá terminando a execução da procedure sem dar COMMIT ou ROLLBACK. Mas se o código tiver muito grande e você não tiver como controlar, você pode executar o seguinte comando

    IF @@TRANCOUNT > 0 COMMIT TRAN

    Isto vai resolver o problema mas não é o mais recomendado. O correto é uma análise do código e colocar COMMIT e ROLLBACK no lugar certo.

    Abraço,
    Lucas Benevides
    DBA cabuloso

    quarta-feira, 25 de julho de 2012 19:19
  • Marcelo,

    Neste caso você poderia trabalhar com transações implicitas, informando para o SQL Server que todos este bloco transacional será uma única transação e você informará o momento do Rollback ou Commit.

    Quando uma conexão trabalha em modo de transação implícita, a instância do Mecanismo de banco de dados do SQL Server iniciará automaticamente uma nova transação depois que a transação atual for confirmada ou revertida. Você não faz nada para determinar o início de uma transação; apenas confirma ou reverte cada uma das transações. O modo de transação implícita gera uma cadeia contínua de transações.

    Para isso você terá que utilizar o comando SET IMPLICIT_TRANSACTION.

    A transação permanecerá em vigor até que você emita uma instrução COMMIT ou ROLLBACK. Após a confirmação ou reversão da primeira transação, a instância do Mecanismo de Banco de Dados iniciará automaticamente uma nova transação da próxima vez que qualquer instrução for executada pela conexão.

    A instância continuará a gerar a cadeia de transações implícitas até que modo de transação implícita seja desativado. O modo de transação implícita é definido tanto pelo uso da instrução SET do Transact-SQL como por meio de funções e métodos API de banco de dados.

    Observação: O modo de transação implícita no SQL Server inicia uma transação se nenhuma já tiver sido inicializada, mas o COMMIT ou o ROLLBACK deve ser definido pelo desenvolvedor.

    Para usar esse tipo de transação, precisamos ativá-la no SQL Server usando o código abaixo:
    SET IMPLICIT_TRANSACTIONS ON;

    Agora execute a instrução abaixo para verificar quantas transações em aberto existem:
    SELECT @@TRANCOUNT;

    Crie uma tabela simples e execute o @@TRANCOUNT novamente:
    CREATE TABLE TESTE (ID INT PRIMARY KEY);
    SELECT @@TRANCOUNT;

    O resultado agora é 1, uma transação foi inicializada. E se executarmos um insert simples nessa tabela?

    INSERT INTO TESTE VALUES (10);
    SELECT * FROM TESTE; SELECT @@TRANCOUNT;

    A instrução insert foi executada, porém continuamos a ter uma única transação. Para finalizar, vamos executar um ROLLBACK:
    ROLLBACK TRAN;
    SELECT @@TRANCOUNT;

    Pronto, nossa transação foi revertida. Se você quiser confirmar, execute um SELECT na tabela TESTE. O que aconteceu?

    Uma mensagem de erro informando que a tabela não existe é exibida, ou seja, nosso ROLLBACK reverteu tudo, desde o insert à criação da tabela.

    Para finalizar, vamos desativar as transações implícitas:
    SET IMPLICIT_TRANSACTIONS OFF;
     

    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    • Sugerido como Resposta Vitor Mendes quinta-feira, 26 de julho de 2012 12:55
    quinta-feira, 26 de julho de 2012 12:48
  • Junior, se eu iniciar  stored1 com begin tran e em determinado momento chamar outra stored2,

    quando finalizo a stored1 se acontecer algum erro tanto em stored1 ou stored2 o rollback de stored1 reverterá tudo que foi feito em stored2 tbm?

    quinta-feira, 26 de julho de 2012 13:01