SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Catch invalid object and rollback transaction


Catch invalid object and rollback transaction

Author
Message
K Currie
K Currie
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 444
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



Luis Cazares
Luis Cazares
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39849 Visits: 19802
Have you tried the TRY..CATCH to handle errors?
http://technet.microsoft.com/en-us/library/ms175976(v=sql.105).aspx


Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
K Currie
K Currie
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 444
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...)
saravanan.a 88885
saravanan.a 88885
SSChasing Mays
SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)

Group: General Forum Members
Points: 607 Visits: 789
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'
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211939 Visits: 46259
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 recoverability

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


Kenny Jozi
Kenny Jozi
SSC Eights!
SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

Group: General Forum Members
Points: 929 Visits: 1398
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
Sean Pearce
Sean Pearce
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3820 Visits: 3436
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search