September 2, 2009 at 11:30 pm
Comments posted to this topic are about the item @@Error and sp_rename
September 3, 2009 at 1:53 am
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."
September 3, 2009 at 1:56 am
sorry, the value of @@error is 0, my mistake
September 3, 2009 at 4:22 am
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"
September 3, 2009 at 6:02 am
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.
September 3, 2009 at 6:13 am
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"
September 3, 2009 at 6:59 am
Chirag (9/3/2009)
This is what i did
begin tryEXECUTE 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!
September 3, 2009 at 10:15 am
Though I selected " 0 " as answer but still not sure about @@error.
SQL DBA.
September 3, 2009 at 11:45 am
SanjayAttray (9/3/2009)
Though I selected " 0 " as answer but still not sure about @@error.
What is not clear?
John Moreno
September 3, 2009 at 12:01 pm
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.
September 3, 2009 at 11:01 pm
sknox (9/3/2009)
Chirag (9/3/2009)
This is what i did
begin tryEXECUTE 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!
OK i seem to get it. So in this case will @@Error always be 0.
"Keep Trying"
September 11, 2009 at 11:25 pm
sknox (9/3/2009)
Chirag (9/3/2009)
This is what i did
begin tryEXECUTE 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!
No, sorry, this explanation is incorrect. As is the answer, at least for SQL Server 2005 and 2008. The only correct answer that I have been able to extract is the one that chirag already demonstrated: 15248.
And for the record: TRY/CATCH does catch the @@error value *after* the stored procedure completes: executing and failing is still a form of execution. The value of @@ERROR after execution is 15248. If you think differently, then please provide evidence.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 14, 2009 at 7:28 am
I apologize to Barry for agreeing with him.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 14, 2009 at 11:44 am
RBarryYoung (9/11/2009)
sknox (9/3/2009)
Chirag (9/3/2009)
This is what i did
begin tryEXECUTE 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!
No, sorry, this explanation is incorrect. As is the answer, at least for SQL Server 2005 and 2008. The only correct answer that I have been able to extract is the one that chirag already demonstrated: 15248.
And for the record: TRY/CATCH does catch the @@error value *after* the stored procedure completes: executing and failing is still a form of execution. The value of @@ERROR after execution is 15248. If you think differently, then please provide evidence.
What kind of evidence?
If I execute:
EXECUTE sp_rename N'fakename', N'fakename2', 'OBJECT'
select @@error
on a SQL 2008 instance, in a database with the compatibility level set to 100, I get results of 0 (No column name) and the message:
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 321
Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.
(1 row(s) affected)
On a SQL 2005 instance in the master database, I get the same resultset and this message:
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 315
Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.
(1 row(s) affected)
I get these resutls when using SSMS 2005 and 2008 and Query Analyzer from SQL 2000.
Only from within the CATCH block do I see the 15248 answer.
September 17, 2009 at 7:23 am
Looks like the moral is that we should test the return code from that procedure...
DECLARE @Return INT
EXECUTE @Return = sp_rename N'fakename', N'fakename2', 'OBJECT'
SELECT @Return
Forget @@Error!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply