Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by John Moreno » @@Error and sp_rename
Add to briefcase
17 posts, Page 1 of 2
12»»

@@Error and sp_rename Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
john.moreno
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: Tuesday, May 14, 2013 10:31 PM
Points: 339, Visits: 950
Comments posted to this topic are about the item @@Error and sp_rename
Post #781930
Adrian Ionita
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
Adrian Ionita
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
ChiragNS
Posted Thursday, September 03, 2009 4:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825
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
sknox
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: Wednesday, May 15, 2013 5:22 AM
Points: 1,037, Visits: 1,354
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
ChiragNS
Posted Thursday, September 03, 2009 6:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825
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
sknox
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: Wednesday, May 15, 2013 5:22 AM
Points: 1,037, Visits: 1,354
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
SanjayAttray
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: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554
Though I selected " 0 " as answer but still not sure about @@error.

SQL DBA.
Post #782350
john.moreno
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: Tuesday, May 14, 2013 10:31 PM
Points: 339, Visits: 950
SanjayAttray (9/3/2009)
Though I selected " 0 " as answer but still not sure about @@error.


What is not clear?

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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:52 PM
Points: 2,548, Visits: 17,348
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
17 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.