I have the following problem. I have a procedure in SQL Server 2008 that has a Cursor. The actions that are executed within the stored procedure are within a transaction to cancel it in case of error, because if only one of them fails, I must cancel the entire process. When the execution is correct there is no problem but when it fails I receive the following message:
The ROLLBACK TRANSACTION request does not have the corresponding BEGIN TRANSACTION.
I've tried putting BEGIN TRANSACTION
before BEGIN TRY
but the error is the same.
DECLARE Lineas_Cursor CURSOR FOR
SELECT .....
OPEN Lineas_Cursor
BEGIN TRY
BEGIN TRANSACTION
FETCH NEXT FROM Lineas_Cursor
INTO .......
WHILE @@FETCH_STATUS = 0
BEGIN
--Actualizo
UPDATE ......
FETCH NEXT FROM Lineas_Cursor
INTO .....
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
ROLLBACK TRANSACTION
END CATCH
CLOSE Lineas_Cursor;
DEALLOCATE Lineas_Cursor;