-
Sep 22nd, 2017, 03:17 AM
#1
[RESOLVED] Transaction will not ROLLBACK
Hello.
I’m having an issue using a transaction.
I’m trying to Insert into a temp table from another table.
I deliberately deleted the actual table to see if I can get a ROLLBACK on the transaction but instead I get this error:
"Msg 208, Level 16, State 1, Procedure spVbudgetInsertUpdateXX, Line 16
Invalid object name ‘zz_tblBUValue_DROP_334’.
Msg 266, Level 16, State 2, Procedure spVbudgetInsertUpdateXX, Line 16
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
"
I have oversimplified the stored procedure so i can only see the Begin ends and the insert and I do not see any mistake.
Any ideas?
Thanks
Code:
ALTER PROCEDURE [dbo].[spVbudgetInsertUpdateXX]
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
SELECT *
INTO #tempbudgetValue
from zz_tblBUValue_DROP_334 D1
where not exists(select Cinemacode from zz_tblBUValue_DROP_335 D2
where D1.CinemaCode = D2.CinemaCode and D1.CinemaClassCode = D2.CinemaClassCode and D1.FMonth = D2.FMonth and D1.FYear= D2.FYear)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Sep 22nd, 2017, 03:27 AM
#2
Re: Transaction will not ROLLBACK
GOT IT!
We need to catch fatal errors (as is the dropped table).
So we use the command SET XACT_ABORT ON
Now it works
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|