Post reply

Get Error Description in SQL Server 2000

  • January 12, 2009 at 9:45 pm

    #198453

    Comments posted to this topic are about the item Get Error Description in SQL Server 2000

  • January 12, 2009 at 9:52 pm

    #925653

    Looks pretty familiar.

    http://www.sqlservercentral.com/articles/Stored+Procedures/capturingtheerrordescriptioninastoredprocedure/1342/[/url]

    --------------------
    Colt 45 - the original point and click interface

  • January 12, 2009 at 10:19 pm

    #925657

    Yes I had a look. It is similar to mine. But I have not extracted any thing from it. If I had done so why would I post the article on same site.:)

  • January 13, 2009 at 10:42 am

    #926078

    I was unable to find sp_GetErrorDesc in the resouce section. Where should (url) I be looking? A search on the procedure name returned no hits.

    -- Mark --

  • January 13, 2009 at 10:59 am

    #926096

    Its there in the Resources Section There's a link to download the file The File Name itself is the link Text

    sp_GetErrorDesc.sql

    Search for this sp_GetErrorDesc.sql

  • January 13, 2009 at 11:01 am

    #926099

    The URL of the file is

    http://www.sqlservercentral.com/Files/sp_GetErrorDesc.sql/2268.sql

  • January 13, 2009 at 3:32 pm

    #926281

    The way I usually do things is to try to check return codes and handle the error at the procedure level...

    I code all my stored procs to return specific codes for specific errors.

    ex:

    exec @ret = mydb.dbo.usp_procedure @arg1='foo'

    if @ret = -1

    RAISERROR('USP_PROCEDURE FAILED BECAUSE IT WAS TIRED', 16,1) WITH LOG

    if @ret = -2

    RAISERROR('USP_PROCEDURE FAILED FOR SOME OTHER REASON', 16,1) -- not specifying WITH LOG

    What this accomplishes is that you keep your error severity level in the 'user mode' and allows you to be specific with your language when dealing with a specific stored procedure.... it also logs (or doesn't) depending on how you code it.... so for a less critical table, a 515 error might not be "log worthy" if you catch my meaning.

    It also enables you to move your database to a different server without as much trouble.

    Craig Outcalt


    Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
    My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

  • January 13, 2009 at 9:31 pm

    #926366

    Hi,

    Actually I had a need to get Exact Error Message and for Emailing. Since I had to give all functionality that a windows service would do.

    Otherwise I would had sticked to Windows Service.

  • January 14, 2009 at 11:05 am

    #926780

    MS SQL 2000 already has system stored procedure in msdb that returns error description from messages table. The system stored procedure is named "sp_get_message_description"

  • January 14, 2009 at 11:22 am

    #926792

    I ran sp_get_message_description 515

    and the O/P

    Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails.

    Can you tell me how to fill out the missing values???

  • January 14, 2009 at 11:36 am

    #926805

    Some error messages are template and need to raiseerror first, check for the error number and get error description, at that point, you will get the target objects (table name, column etc ..) replaced. This applies to some errors, but others you can get the full error message such as divide by zero etc ..

    exec msdb..sp_get_message_description 8134

    --Mohamed

  • January 14, 2009 at 11:43 am

    #926810

    I had a requirement to get the Exact Error Messages generated by SQL Server 2000. If I was not able to achieve that then we would have switched to Windows Service.

  • January 15, 2009 at 1:07 pm

    #927659

    Is it me or are you guys missing a trick here?

    There is a system table named sysmessages where all error descriptions are stored (both system errors and user errors), with reference to the place holders you should know for the most part which table caused the error as you should really be checking for errors at every stage of a transaction. With regards to the column this is where my process falls short, you are not able able to capture it real time you would have to re-run the process to find the column that isn't happy.

    Once I'm in the office tomorrow I will post the full code I use for all error captures for all my databases. This code allows you to trap the error, record the error message and the details of the process that caused the error and then issue an alert email to the DBA's or whoever you want to send the message to.

    The error capture process will store the error details and process details in the database you are working with if for any reason it cannot record the error in the database it reverts to storing the data in the server log. I have to say I'm not a big fan of storing the error in the log as it is more difficult to extract in an automated manor than if it is stored in table where the error occurred or even in a central error capture database.

  • January 15, 2009 at 9:35 pm

    #927838

    The sysmessage is there but since there are multiple insert stmts and multiple update stmts just looking at the placeholder is very difficult. Since in my case the support IT team required the exact error description so that they can sort the issue as soon as possible. since sys messages give only the template message. for eg Inserting Null value into a column which does not allow it. In our case each table has around 15 - 25 columns. So it would be very difficult to find out looking at the sysmessage that which table the issue has occurred and which value was Null.

  • January 18, 2009 at 4:45 pm

    #928983

    I found both the article AND the subsequent discussion here very useful. Thank you to all!

Viewing 15 posts - 1 through 14 (of 14 total)

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