sql server - procedimientos - El recuento de transacciones después de EXECUTE indica un número incorrecto de BEGIN y COMMIT

transacciones en sql server pdf

Question

Cuando se produce un error en el siguiente proceso, el mensaje de error es "Recuento de transacciones después de que EXECUTE indica un número incorrecto de instrucciones BEGIN y COMMIT. count = 1, current count = 0 previo count = 1, current count = 0 El recuento de transacciones después de EXECUTE indica un número incorrecto de instrucciones BEGIN y COMMIT count = 1, current count = 0 previa count = 1, current count = 0 "

CREATE PROCEDURE [dbo].[proc_PurchaseOrder_Create](@CustomerID INT, @CustomerOrderID INT OUTPUT)          
AS          
SET NOCOUNT ON;          
--------------------------------          
DECLARE @Return_Message VARCHAR(1024)          
DECLARE @ErrorCode  INT            
DECLARE @ErrorStep  VARCHAR(200)          
DECLARE @UserID INT  
--------------------------------          
DECLARE @CustomerCartID INT,@PONumber VARCHAR(255), @CartTotalAmount NUMERIC          

BEGIN TRY          

    BEGIN TRAN                  
  -----------------------------------------------------------------------------             
  SELECT @ErrorCode = @@ERROR          
      SET NOCOUNT ON;      
  IF NOT EXISTS(SELECT CustomerCartID FROM TxnCustomerCart WHERE CustomerID = @CustomerID)           
     BEGIN          
        SET @Return_Message= ''No Cart item is available''          
           SET @ErrorCode = 1          
        END                  
        ELSE          
        BEGIN          
    -----------------------------------------------------------------------------          
    SELECT @UserID = ISNULL(UserID,0) FROM TxnCustomers WHERE CustomerID = @CustomerID      
    -----------------------------------------------------------------------------          
    SELECT @CartTotalAmount = CartTotalAmount, @CustomerCartID = CustomerCartID FROM TxnCustomerCart WHERE @CustomerID = CustomerID            
    SELECT @ErrorStep = ''Error on inserting data into TxnCustomerOrders'';            
    INSERT INTO TxnCustomerOrders(CustomerID,OrderDate,OrderStatus,Ramarks,PaymentCategoryCode,CreatedBy,CreatedDatetime)          
    VALUES(@CustomerID,GETDATE(),''PRTRCVD'','''',''Wallet'',@UserID,GETDATE())            
    SET @CustomerOrderID = SCOPE_IDENTITY();          

    SELECT @ErrorStep = ''Error on generating PONumber'';            
    SET @PONumber =dbo.fun_getPONumber(@CustomerID, @CustomerOrderID)          

    SELECT @ErrorStep = ''Error on inserting data into TxnPurchaseOrder'';          

    INSERT INTO TxnPurchaseOrder (CustomerOrderID, PONumber, PODate, POAmount, PODocID, PODocPath, CreatedBy, CreatedDate)          
     VALUES (@CustomerOrderID, @PONumber, GETDATE(), @CartTotalAmount, REPLACE(@PONumber, ''/'' , ''-''), REPLACE(@PONumber, ''/'' , ''-'') + ''.pdf'', @UserID, GETDATE())              

    SELECT  @ErrorCode  = 0, @Return_Message = ''Purchase order successfully created''          
  END       

  SET NOCOUNT OFF;         
    COMMIT TRAN               
    RETURN @ErrorCode -- =0 if success,  <>0 if failure          
END TRY           
BEGIN CATCH          
    IF @@TRANCOUNT > 0 ROLLBACK          

    SELECT @ErrorCode = ERROR_NUMBER()          
        , @Return_Message = @ErrorStep + '' ''          
        + cast(ERROR_NUMBER() as varchar(20)) + '' line: ''          
     + cast(ERROR_LINE() as varchar(20)) + '' ''           
        + ERROR_MESSAGE() + '' > ''           
        + ERROR_PROCEDURE()          

    RETURN @ErrorCode -- =0 if success,  <>0 if failure          

END CATCH  

Answer 1

También debe considerar los resultados de la función XACT_STATE (). Compruebe el bloque Transacciones no confirmables y XACT_STATE en MSDN

EDITAR MÁS TARDE

Cambié de opinión sobre una posible solución :) Finalmente conseguí reproducir tu situación. Mira. Tomé su procedimiento, eliminé todos los códigos, excepto el código relacionado con el manejo de excepciones, e try/catch lógica y la expresión agregada, lo que provocará un error en la resolución del nombre del objeto (error fatal). Esto es lo que obtuve

CREATE PROCEDURE [dbo].[proc_PurchaseOrder_Create]         
AS          
SET NOCOUNT ON;          
BEGIN TRY          

    BEGIN TRAN   

    print ''before''
    select * from ##global_tmp_table; --> fatal error here
    print ''after''

    COMMIT TRAN         
END TRY           
BEGIN CATCH
    print ''catch''
    IF @@TRANCOUNT > 0 ROLLBACK          
END CATCH  

Ok, ahora si trato de ejecutar este procedimiento obtuve la siguiente salida

antes Msg 208, nivel 16, estado 0, procedimiento proc_PurchaseOrder_Create, línea 29 Nombre de objeto no válido ''## global_tmp_table''. Msg 266, nivel 16, estado 2, procedimiento proc_PurchaseOrder_Create, línea 29 Recuento de transacciones después de EXECUTE indica un número incorrecto de instrucciones BEGIN y COMMIT. Recuento previo = 0, recuento actual = 1.

¿Entonces qué pasó? - Nuestra transacción permanece abierta ; puede verificar esto ejecutando print @@TRANCOUNT en la misma ventana

¿Por qué sucedió esto? - porque el bloque catch no puede detectar errores fatales - del mismo artículo de MSDN que mencioné anteriormente

Los siguientes tipos de errores no son manejados por un bloque CATCH cuando ocurren en el mismo nivel de ejecución que la construcción TRY ... CATCH: Compila errores, como errores de sintaxis, que impiden la ejecución de un lote. Errores que ocurren durante la recompilación de nivel de sentencia, como los errores de resolución de nombre de objeto que ocurren después de la compilación debido a la resolución diferida del nombre.

Antes de que arreglemos esta situación, debe llamar a ROLLBACK en la ventana donde intentó ejecutar el procedimiento almacenado para finalmente cerrar nuestra transacción.

¿Y cómo podemos arreglar eso? - solo tenemos que activar set XACT_ABORT ON - puede encontrar un ejemplo en el mismo artículo en el uso de TRY ... CATCH con el bloque XACT_STATE

Entonces, finalmente, nuestro procedimiento de prueba debería verse así

ALTER PROCEDURE [dbo].[proc_PurchaseOrder_Create]         
AS          
SET NOCOUNT ON;  
SET XACT_ABORT ON; --> the only change
BEGIN TRY          

    BEGIN TRAN   

    print ''before''
    select * from ##global_tmp_table;
    print ''after''

    COMMIT TRAN         
END TRY           
BEGIN CATCH
    print ''catch''
    IF @@TRANCOUNT > 0 ROLLBACK          
END CATCH 

Y ahora, en un caso de motor de base de datos de error fatal, la transacción se retrotrae automáticamente y obtendrá solo el mensaje de error esperado

before Msg 208, Level 16, State 0, Procedure proc_PurchaseOrder_Create, Line 31 Nombre de objeto no válido ''## global_tmp_table''.