Identify Pre-Defined and User-Defined Errors in SQL Server

There are around 50,000 predefined error messages that are identified and stored in SQL Server – sys.messages table. Have a preview of these messages to make available a suitable exception catcher in your front end, according to the content usage.

Syntax:

Select message_id, language_id, severity, is_event_logged, text from sys.messages

Arguments:

  • message_id

    ID of the message. Is unique across server. Message IDs less than 50000 are system messages.

  • language_id

    Language ID for which the text in text is used, as defined in syslanguages.

  • Severity

    Severity level of the message, between 1 and 25.

  • is_event_logged

    1 = Message is event-logged when an error is raised.

  • Text

    Description for the occurred error.

User defined messages can as well be stored in this table using sp_addmessage.

Syntax:

sp_addmessage [ @msgnum = ] msg_id , [ @severity = ] severity , [ @msgtext = ] 'msg'
[ , [ @lang = ] 'language' ]
[ , [ @with_log = ] { 'TRUE' | 'FALSE' } ]
[ , [ @replace = ] 'replace' ]


Arguments:

[ @msgnum = ] msg_id
Is the ID of the message.
[ @severity = ] severity
Is the severity level of the error. Valid levels are from 1 through 25.
[ @msgtext = ] 'msg'
Is the text of the error message.
[ @lang = ] 'language'
Is the language for this message.
[ @with_log = ] { 'TRUE' | 'FALSE' }


Is whether the message is to be written to the Windows application log when it occurs.

Ebook Download
View all
Learn
View all