Transaction Error

Question

Transaction Error

Chris-232075

SSCrazy

Points: 2130
More actions
August 9, 2005 at 2:01 pm

#90577

I have the following script that I'm running in query analyzer:
BEGIN TRAN
UPDATE JOBS_Openings
SET [ID] = SUBSTRING(AARCount, CHARINDEX('(', AARCount) + 1, (CHARINDEX(')', AARCount) - CHARINDEX('(', AARCount)) - 10)
IF @@ERROR <> 0 ROLLBACK TRAN
UPDATE JOBS_Openings
SET PrimarySubjectCode = SUBSTRING(AARPrimary, CHARINDEX('(', AARPrimary) + 1, 2)
WHERE CHARINDEX('(', AARPrimary) > 0
IF @@ERROR <> 0 ROLLBACK TRAN
COMMIT TRAN
Please note that the first update statement in this transaction purposely generates an error for testing. However, the second update statement is executed in spite of the ROLLBACK request in the previous update. In addition, I receive the following error message:
"The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."
Would somebody please explain the problem with my syntax? Why isn't my transaction rolled back when the first update statement in the transaction generates an error? Why doesn't my COMMIT TRANS find the corresponding BEGIN TRANS?
Thanks in advance for any help you can provide!
Chris

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

its possible the transaction is open from your testing. on that connection. save the query close the connection and start a new one.

or

just execute rollback tran until it comes back with that message

Otherwise, I think it would be okay.

BEGIN TRAN

UPDATE JOBS_Openings

SET [ID] = SUBSTRING(AARCount, CHARINDEX('(', AARCount) + 1, (CHARINDEX(')', AARCount) - CHARINDEX('(', AARCount)) - 10)

IF @@ERROR <> 0

goto Error

UPDATE JOBS_Openings

SET PrimarySubjectCode = SUBSTRING(AARPrimary, CHARINDEX('(', AARPrimary) + 1, 2)

WHERE CHARINDEX('(', AARPrimary) > 0

IF @@ERROR <> 0

goto Error

COMMIT TRAN

Error:

begin

rollback tran

return

end

Chris-232075 SSCrazy Points: 2130 More actions · Answer 2

Thanks so much for your reply.

Per your suggestion, I disconnected, re-connected, and ran the transaction again. Unfortunately, nothing changed.

I then ran your revised version of the transaction using the Error block, and it worked with my purposely error-prone update statement. The transaction appears to have been properly rolled back (thanks).

However - a new problem. After removing the error from the first update statement, both updates ran properly, but a new error message was generated by the ROLLBACK TRAN request in error block:

"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."

While the transaction does work as it should, I would feel better if I could find the correct syntax to run a transaction without getting such an error message under any scenario?

I'd appreciate any further suggestions.

Thanks again.

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Sorry, you need a return statement after the commit.

BEGIN TRAN

UPDATE JOBS_Openings

SET [ID] = SUBSTRING(AARCount, CHARINDEX('(', AARCount) + 1, (CHARINDEX(')', AARCount) - CHARINDEX('(', AARCount)) - 10)

IF @@ERROR <> 0

goto Error

UPDATE JOBS_Openings

SET PrimarySubjectCode = SUBSTRING(AARPrimary, CHARINDEX('(', AARPrimary) + 1, 2)

WHERE CHARINDEX('(', AARPrimary) > 0

IF @@ERROR <> 0

goto Error

COMMIT TRAN

Return

Error:

begin

rollback tran

return

end

Chris-232075 SSCrazy Points: 2130 More actions · Answer 4

Works great - thank you.

If, by any chance, you could explain why my original code didn't work, I'd really appreciate it.

Thanks again for your time.

noeld SSC Guru Points: 96590 More actions · Answer 5

Chris,

Rollback does not ends the batch or workflow, it simply ... rollsback (@@Trancount =0) all previously started transactions

I the case where the return was missing you get the error because after the Commit, which does not ends the batch either, you are executing a rollback when there were no pending transactions because commit closed the pending one.

hth

* Noel