December 18, 2009 at 8:26 am
Sorry, I know this topic is all over but I just don't see what's wrong... a co-worker of mine presented me with the following test block of code. The 2nd insert is intentionally set to fail. The error is "Msg 208, Level 16, State 1, Line 14 Invalid object name 'hpl.bugssss'."
Why is the CATCH block never reached? When you try to close the query window, you get the message "There are uncommitted transactions, would you like to...." even though the first insert HAS persisted to the database.
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO hpl.bugs (hosp_id, category, bug_name, title, active, updatedby)
VALUES ('0992', 'Category 1', 'Bug Name', 'C', 1, '099200001')
--this table does not exist!!!
INSERT INTO hpl.bugssss (hosp_id, category, bug_name, title, active, updatedby)
VALUES ('0992', 'Category 1', 'Bug Name', 'D', 1, '099200001')
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
print 'got here'
print ERROR_MESSAGE() + ' Error #' + CAST(ERROR_NUMBER() AS varchar)
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
December 18, 2009 at 8:33 am
Please see the section "Errors Unaffected by a TRY…CATCH Construct"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply