Post reply

Retrieving SQL Error Message

  • November 15, 2005 at 12:49 am

    #92245

    I am under the situation where I have to trap the exact error message returned by sql server and save it a table. Upto now I'm only able to get the error id using @@ERROR. Is there anyway that I could get the exact error message thrown by an sql statement (for example: the error message given when I try to insert a duplicate record), into a variable?

    Thanks

  • November 15, 2005 at 7:04 am

    #603898

    Are you wantimg to do this from within a Stored Procedure?

  • November 15, 2005 at 7:28 am

    #603908

    Hi Gogula...you could capture @@error in a variable and then use it to query the sysmessages table in the master db..something like this...

    select description from master.dbo.sysmessages
where error = @errNum  --(variable that stores @@error)







    **ASCII stupid question, get a stupid ANSI !!!**

  • November 15, 2005 at 10:37 pm

    #604089

    Hi Guys,

    Thanks for replying. Yes I want to do it from within a stored procedure. I had already thought of the method which you have suggested Sushila, but I think it would give me the error message along with the placeholders instead of the actual error which is displayed at the time of the error, i.e. with the object names and values and so forth.

    There is also a function called FormatMessage which will give the error message from the sysmessages table when the error number is given. But it also expects the parameters for the placeholders.

    The problem I'm facing is that the SP I'm writing should store in a table whatever error message is returned, exactly as it is returned.

    Thanks again.

  • November 16, 2005 at 6:04 am

    #604169

    You can't do this from within a proc - simple as that (unfortunately)

    The one and only place to grab the complete errormessagetext for all variants of errors, is at the calling client side.

    /Kenneth

  • November 16, 2005 at 7:13 am

    #604192

    FWIW, check this out:

    http://www.sommarskog.se/error-handling-II.html

    http://www.sommarskog.se/error-handling-I.html

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply