ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sql, sql-server-2008, transactions

Solution

The error you are getting is because you are rolling back without having an open transaction (you have either already committed or rolled-back). Consider cleaning up the structure of your stored proc, try executing your entire stored proc as one transaction, and then rolling back if an error occurs. You can also test if a rollback is required by checking if a transaction is open:

BEGIN TRANSACTION;
BEGIN TRY

   --execute all your stored proc code here and then commit
   COMMIT;

END TRY
BEGIN CATCH

   --if an exception occurs execute your rollback, also test that you have had some successful transactions
   IF @@TRANCOUNT > 0 ROLLBACK;  

END CATCH

Problem

I've tried putting the COMMIT TRAN in a if else loop, and I'm still getting this error. I have to enroll a student in a class. If the number of seats after enrollment falls in negative, I have to reverse it and print a message saying can't enroll. I have put other error messages just to see how transactions work. ``` CREATE PROCEDURE dbo.EnrollStudent ( @CourseID AS INTEGER, @StudentID AS VARCHAR(20) ) AS BEGIN DECLARE @StatusID INTEGER DECLARE @Status VARCHAR(50) DECLARE @CurrentSeats INTEGER DECLARE @ErrorCode INTEGER SET @StatusID=0 IF EXISTS (SELECT 1 FROM dbo.CourseEnrollment WHERE dbo.CourseEnrollment.CourseId=@CourseID AND dbo.CourseEnrollment.StudentId=@StudentID ) BEGIN BEGIN TRAN Tr1 SET @StatusID = 1 SELECT @ErrorCode=@@ERROR IF (@ErrorCode<>0) GOTO OTHERPROBLEM ELSE COMMIT TRAN Tr1 END IF EXISTS ( SELECT 1 FROM dbo.CourseEnrollment FULL OUTER JOIN dbo.Courses ON dbo.Courses.CourseId=@CourseID WHERE dbo.CourseEnrollment.StudentId<>@StudentID AND dbo.Courses.Faculty IS NULL ) BEGIN BEGIN TRAN Tr2 SET @StatusID=2 SELECT @ErrorCode=@@ERROR IF (@ErrorCode<>0) GOTO OTHERPROBLEM2 ELSE COMMIT TRAN Tr2 END IF @StatusID=0 BEGIN IF EXISTS ( SELECT 1 FROM dbo.Courses WHERE dbo.Courses.CourseId=@CourseID AND dbo.Courses.Faculty IS NOT NULL ) BEGIN BEGIN TRAN Tr3 SET @StatusID=3 BEGIN TRAN InsertingValues INSERT INTO dbo.CourseEnrollment (dbo.CourseEnrollment.StudentId,dbo.CourseEnrollment.CourseId) VALUES (@StudentID,@CourseID); SELECT @ErrorCode=@@ERROR IF (@ErrorCode<>0) GOTO InsertProblem ELSE COMMIT TRAN InsertingValues BEGIN TRAN UpdateCourses UPDATE dbo.Courses SET OpenSeats = OpenSeats-1 WHERE dbo.Courses.CourseId = @CourseID SELECT @ErrorCode=@@ERROR IF (@ErrorCode<>0) GOTO UpdateProblem ELSE COMMIT TRAN UpdateCourses SELECT @CurrentSeats=OpenSeats FROM dbo.Courses WHERE dbo.Courses.CourseId = @CourseID IF (@CurrentSeats<0) GOTO PROBLEM ELSE COMMIT TRAN Tr3 END END OTHERPROBLEM: BEGIN PRINT 'Unable to set status' ROLLBACK TRAN END OTHERPROBLEM2: BEGIN PRINT 'Unable to set status' ROLLBACK TRAN END UpdateProblem: BEGIN PRINT 'Not able to update values' ROLLBACK TRAN InsertingValues END InsertProblem: BEGIN PRINT 'Not able to insert' ROLLBACK TRAN InsertingValues END PROBLEM: BEGIN PRINT 'Seats Full!' ROLLBACK TRAN END IF @StatusID = 1 BEGIN SET @Status = 'The Student is already enrolled' END; ELSE IF @StatusID = 2 BEGIN SET @Status = 'Cannot enroll until faculty is selected' END ELSE IF @StatusID = 3 BEGIN SET @Status = 'Student Enrolled' END SELECT @Status END; ``` This correctly updated the tables, but is giving the following errors: ``` (1 row(s) affected) (1 row(s) affected) Unable to set status Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 101 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. Unable to set status Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 108 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. Not able to update values Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 115 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. Not able to insert Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 123 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. Seats Full! Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 131 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. (1 row(s) affected) ```

Original source