@@Error and sp_rename

  • Comments posted to this topic are about the item @@Error and sp_rename

  • 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."

  • sorry, the value of @@error is 0, my mistake

  • 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"

  • 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.

  • 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"

  • 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!

  • Though I selected " 0 " as answer but still not sure about @@error.

    SQL DBA.

  • SanjayAttray (9/3/2009)


    Though I selected " 0 " as answer but still not sure about @@error.

    What is not clear?

    John Moreno

  • 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.

  • sknox (9/3/2009)


    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!

    OK i seem to get it. So in this case will @@Error always be 0.

    "Keep Trying"

  • sknox (9/3/2009)


    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!

    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]

  • 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

  • RBarryYoung (9/11/2009)


    sknox (9/3/2009)


    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!

    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.

  • 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