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?
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.
User contributions licensed under CC BY-SA 3.0