The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction

0

I am getting the following error message randomly, whenever I am trying to execute the below stored procedure through the .net core application.

Error Message:

System.Data.SqlClient.SqlException (0x80131904): The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

Stored Procedure:

ALTER PROCEDURE [dbo].[USP_UpdateInfo] (@xx NVARCHAR(50),
            @yy NVARCHAR(200),@Id int, @ab NVARCHAR(max),
            @ac int,
            @ad NVARCHAR(200), @ae NVARCHAR(200),@af NVARCHAR(100),
            @ag NVARCHAR(100),@zz NVARCHAR(MAX), @DateTime DATETIME)
  AS
   BEGIN
BEGIN TRANSACTION;
SAVE TRANSACTION UpdateInfo;
BEGIN TRY
    --First update 
    UPDATE XXTable
    SET XX = @xx
    WHERE YY=@yy;

    --Update the XXTable object with updated information 
    UPDATE XXTable
    SET ZZ = @zz,
    AB = @ab,
    AC= @ac,
    AD= @ad,
    AE= @ae,
    AF= @af,
    AG= @ag,
    DateTime= @DateTime
    WHERE Id=@Id;

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION UpdateInfo; -- rollback to MySavePoint
    END
END CATCH
COMMIT TRANSACTION  

END

So, can anyone please help me out on this issue?

azure
asp.net-core
stored-procedures
azure-sql-database
asked on Stack Overflow Jun 13, 2019 by Pradeep • edited Jun 13, 2019 by Pradeep

1 Answer

0

The entire transaction be rolled back with or without the savepoint in the case of a failover. I suggest you simply remove that unneeded complexity here.

Don't know if your code was overly simplified for posting here but your catch block hides the error entirely. One should generally include THROW; after the ROLLBACK to re-raise the original error and terminate proc execution.

answered on Stack Overflow Jun 13, 2019 by Dan Guzman

User contributions licensed under CC BY-SA 3.0