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;