When Adaptive Server encounters a problem, it displays information—in an error message that describes whether the problem is caused by the user or the system—about the problem, how serious it is, and what you can do to fix it. The error message consists of:
A message number, which uniquely identifies the error message
A severity level number between 10 and 24, which indicates the type and severity of the problem
An error state number, which allows unique identification of the line of Adaptive Server code at which the error was raised
An error message, which tells you what the problem is, and may suggest how to fix it
For example, this is what happens if you try to access a table that does not exist:
select * from publisher
Msg 208, Level 16, State 1: publisher not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
In some cases, there can be more than one error message for a single query. If there is more than one error in a batch or query, Adaptive Server usually reports only the first one. Subsequent errors are reported the next time you execute the batch or query.
The error messages are stored in master..sysmessages, which is updated with each new release of Adaptive Server. Here are the first few rows (from an Adaptive Server with us_english as the default language):
select error, severity, description from sysmessages where error >=101 and error <=106 and langid is null
error severity description ----- -------- -------------------------------------------------- 101 15 Line %d: SQL syntax error. 102 15 Incorrect syntax near '%.*s'. 103 15 The %S_MSG that starts with ’%.*s’ is too long. Maximum length is %d. 104 15 Order-by items must appear in the select-list if the statement contains set operators. 105 15 Unclosed quote before the character string '%.*s'. 106 16 Too many table names in the query. The maximum allowable is %d. (6 rows affected)
You can generate your own list by querying sysmessages. Here is some additional information for writing your query:
If your server supports more than one language, sysmessages stores each message in each language. The column langid is NULL for us_english and matches the syslanguages.langid for other languages installed on the server. For information about languages on your server, use sp_helplanguage.
The dlevel column in sysmessages is currently unused.
The sqlstate column stores the SQLSTATE value for error conditions and exceptions defined in ANSI SQL92.
Message numbers 17000 and greater are system procedure error messages and message strings.