Catch invalid object and rollback transaction
-
I have some code with transaction logic in it. When an update fails, I want the transaction to rollback.
The problem I'm having is if my transaction fails because of an invalid object name, it doesn't capture an error number and leaves my transaction open. Is there a better way to capture this update error and rollback the transaction?
BEGIN TRANSACTION
print 'error1'
print @@Error
-- @@Error is 0
UPDATE a
SET b = 1
FROM a,c
WHERE a.t = c.t
-- table c does not exist, so the update statement fails
print 'error2'
print @@Error
-- nothintg prints
IF @@ERROR <> 0
BEGIN
GOTO ErrorTrans
END
ErrorTrans:
print 'in errortrans'
ROLLBACK TRANSACTION
-
Have you tried the TRY..CATCH to handle errors?
http://technet.microsoft.com/en-us/library/ms175976(v=sql.105).aspx
-
I understand my invalid object name error is a "compile error" and this wouldn't get caught in @@Error and that's why my code was stopping/failing. I believe the better way to catch something like this is an If exists:
if exists (select * from sysobjects where name = 'a')
Begin
update....
Is there a different way? What if the syntax error isn't in the table name but in a column name? I'm assuming I would have to verify each column existed first as well. Any other thoughts? (maybe folks should learn how to type and test instead of all this...)
-
Hi Try the following......
if exists(select * from sysobjects where name ='a')
begin
print 'passed'
UPDATE a
SET a.b = 1
FROM a,c
WHERE a.t = c.t
end
else
print 'Failed'
-
K Currie (10/8/2013)
What if the syntax error isn't in the table name but in a column name?If it's a missing column, the query won't even parse and won't start executing at all (missing tables allow for deferred compile, missing columns don't), so no transaction will ever start.
btw, sys.objects or sys.tables, not sysobjects. The latter is deprecated, included only for backward compatibility with SQL 2000 and should not be used.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Use TRY and CATCH
BEGIN TRY-- Table does not exist; object name resolution
-- error not caught.
SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
check BOL
http://technet.microsoft.com/en-us/library/ms130214.aspx
SQL 2000/2005/2008/2012 DBA - MCTS/MCITP
-
kenneth.mofokeng (10/9/2013)
Use TRY and CATCHBEGIN TRY-- Table does not exist; object name resolution
-- error not caught.
SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
check BOL
Eish, Kenneth my bra.
That example in books online shows how control is NOT passed to the catch block.
You missed the surrounding text on the example:
The following example shows how an object name resolution error generated by a SELECT statement is not caught by the TRY…CATCH construct.
The error is not caught and control passes out of the TRY…CATCH construct to the next higher level.
Eita
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply