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 12»»

@@Error and sp_rename Expand / Collapse
Author
Message
Posted Wednesday, September 02, 2009 11:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:20 PM
Points: 341, Visits: 1,031
Comments posted to this topic are about the item @@Error and sp_rename
Post #781930
Posted Thursday, September 03, 2009 1:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 07, 2012 6:49 AM
Points: 8, Visits: 22
The correct answer is 11.

EXECUTE sp_rename N'fakename', N'fakename2', 'OBJECT'

"Msg 15248, Level 11, State 1, Procedure sp_rename, Line 321
Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong."
Post #781970
Posted Thursday, September 03, 2009 1:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 07, 2012 6:49 AM
Points: 8, Visits: 22
sorry, the value of @@error is 0, my mistake
Post #781972
Posted Thursday, September 03, 2009 4:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:27 AM
Points: 2,366, Visits: 1,837
I found the question confusing. It asks for value of @@Error which is 15248. I put the statement in a TRY/CATCH and got the @@Error as 15248.

"Keep Trying"
Post #782056
Posted Thursday, September 03, 2009 6:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:12 AM
Points: 1,247, Visits: 1,594
Chirag (9/3/2009)
I found the question confusing. It asks for value of @@Error which is 15248. I put the statement in a TRY/CATCH and got the @@Error as 15248.


The question is "What is the value of @@Error after executing this statement:".
A TRY/CATCH will catch errors which happen during execution.

If you run this code:

EXECUTE sp_rename N'fakename', N'fakename2', 'OBJECT'
SELECT @@Error

you will see the error 15248 message, but @@Error will be 0.

Post #782090
Posted Thursday, September 03, 2009 6:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:27 AM
Points: 2,366, Visits: 1,837
This is what i did
begin try
EXECUTE sp_rename N'fakename', N'fakename2', 'OBJECT'
end try
begin catch
select @@ERROR
end catch



"Keep Trying"
Post #782098
Posted Thursday, September 03, 2009 6:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:12 AM
Points: 1,247, Visits: 1,594
Chirag (9/3/2009)
This is what i did
begin try
EXECUTE sp_rename N'fakename', N'fakename2', 'OBJECT'
end try
begin catch
select @@ERROR
end catch



Yes. That will catch the error as it happens. So you determined the value of @ERROR when an error arises during execution. But the question asked about the value of @@ERROR AFTER execution.

Because the error is not severe enough to stop execution, and the sp executes at least one successful statement after the error (at least the statement to set the return value), the value of @@ERROR after execution is 0.

This is especially important to consider when working with different versions of SQL Server. While you can use a TRY/CATCH in new versions of SQL Server, a lot of systems still run on 2000, which does not support TRY/CATCH. As a consequence, there is a lot of code which relies on selecting @@ERROR after execution; also, a lot of programmers still think this way, even if they're programming on SQL 2005+. Old habits can be hard to break!
Post #782122
Posted Thursday, September 03, 2009 10:15 AM
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: Thursday, April 17, 2014 11:58 AM
Points: 3,924, Visits: 1,588
Though I selected " 0 " as answer but still not sure about @@error.

SQL DBA.
Post #782350
Posted Thursday, September 03, 2009 11:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:20 PM
Points: 341, Visits: 1,031
SanjayAttray (9/3/2009)
Though I selected " 0 " as answer but still not sure about @@error.


What is not clear?

John Moreno
Post #782434
Posted Thursday, September 03, 2009 12:01 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 04, 2014 8:25 AM
Points: 2,602, Visits: 17,845
I'm going to guess that this happens because sp_rename has a RETURN 1 right after the RAISERROR to break out of the procedure and the RETURN succeeds, resetting the value of @@ERROR before the procedure completes.
Post #782453
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse