SQL Server Performance

Error trapping help needed...

Discussion in 'General Developer Questions' started by zev_steinhardt, Sep 23, 2003.

  1. zev_steinhardt New Member

    A co-worker of mine is working on a script and is trying to trap an error. The script he has is as follows

    BEGIN TRANSACTION a
    SELECT CONVERT(int, 'k') -- purposely creating an error

    IF (@@ERROR <> 0)
    BEGIN
    PRINT 'Error'
    ROLLBACK TRANSACTION a
    END

    IF (@@ERROR = 0)
    BEGIN
    PRINT 'No Error'
    COMMIT TRANSACTION a
    END

    He's trying to get the 'Error' message to print. However, when the script is run, it stops after the failed conversion. How can we get the error message to print?

    Zev Steinhardt
  2. gaurav_bindlish New Member

    How are you trying to execute this statement?

    Query Analyzer?
    Do you get the error message thrown by SQL Server in the results window?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. gaurav_bindlish New Member

    On Second thoughs, the second @@error statement, IF (@@ERROR = 0) will check for errors in the last executed statement i.e. IF (@@ERROR <> 0 so the second statement becomes ineffective. You have to capture the error in a local variable to be able to work on the same.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  4. Luis Martin Moderator

    I think, but no sure, may be is compilation problem and no execution problem.
    I mean, commit or rollback is post-compilation.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  5. Jon M Member


    I will assume that you are executing this statement through QA. The line
    "SELECT CONVERT(int, 'k') -- purposely creating an error" will produce this error message:

    "Server: Msg 245, Level 16, State 1, Line 1
    Syntax error converting the varchar value 'k' to a column of data type int."

    You may achieve trapping the error as far as the SQL statement is concerned. If you're using ASP or Visual Basic, the "On Error" can handle such error. Also, I have not tried the WHENEVER statement but check BOL for details.

    Jon M
  6. Twan New Member

    As Jon has hinted...

    there are some errors which cause SQL to rollback the transaction and stop any further processing. The error message for these must be trapped on the client and can't be trapped on the server

    Cheers
    Twan

Share This Page