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''.