Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Catch invalid object and rollback transaction Expand / Collapse
Author
Message
Posted Tuesday, October 8, 2013 9:22 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 10:12 AM
Points: 65, Visits: 402
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


Post #1502903
Posted Tuesday, October 8, 2013 9:52 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:07 PM
Points: 3,559, Visits: 7,679
Have you tried the TRY..CATCH to handle errors?
http://technet.microsoft.com/en-us/library/ms175976(v=sql.105).aspx



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1502905
Posted Tuesday, October 8, 2013 10:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 10:12 AM
Points: 65, Visits: 402
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...)
Post #1502908
Posted Wednesday, October 9, 2013 12:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:53 AM
Points: 224, Visits: 350
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'
Post #1502928
Posted Wednesday, October 9, 2013 1:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:58 AM
Points: 42,832, Visits: 35,964
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1502946
Posted Wednesday, October 9, 2013 2:02 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 330, Visits: 702
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 2005/2008 DBA - MCTS/MCITP
Post #1502955
Posted Wednesday, October 9, 2013 3:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 883, Visits: 2,805
kenneth.mofokeng (10/9/2013)
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


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




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1502983
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse