{[['
']]}
']]}
sp_addmessage system stored procedure
The sp_addmessage system stored procedure lets you add error messages to SQL Server that can be referenced in code. This is helpful for standardized error messages that will be used throughout your application, especially if they need to be able to support multiple languages.
Syntax
sp_addmessage [ @msgnum = ] msg_id ,
[ @severity = ] severity ,
[ @msgtext = ] 'msg'
[ , [ @lang = ] 'language' ]
[ , [ @with_log = ] 'with_log' ]
[ , [ @replace = ] 'replace' ]
Arguments
[ @msgnum= ] msg_id
This is a required value and is the error message number that will be used to call the error message. This must be a whole number greater than 50000. The @msgnum value can be left empty to have an available error message number assigned. When putting error messages into the database in multiple languages the same error number must be used for all the languages.
[ @severity = ]severity
This is the severity or priority of the error message. Error messages are defined with specific severities, but any severity can be specified when an error is raised by the RAISERROR statement in T-SQL. The @severity value must be a number between 1 and 25.
[ @msgtext = ] 'msg'
Is the text of the error message. msg is nvarchar(255) with a default of NULL.
[ @lang = ] 'language'
Is the language for this message. language is sysname with a default of NULL. Because multiple languages can be installed on the same server, language specifies the language in which each message is written. When language is omitted, the language is the default language for the session.
[ @with_log = ] { 'TRUE' | 'FALSE' }
Is whether the message is to be written to the Windows application log when it occurs. @with_log is varchar(5) with a default of FALSE. If TRUE, the error is always written to the Windows application log. If FALSE, the error is not always written to the Windows application log but can be written, depending on how the error was raised. Only members of the sysadmin server role can use this option.
[ @replace = ] 'replace'
This parameter tells the SQL Server to replace an existing error message in sys.messages system catalog. This parameter is varchar(7) and allows only a single value which is “REPLACE”. To use the @replace parameter the @msgnum parameter must be specified. When you replace the us_english version of the error message the severity for all other language specific versions will be updated.
Levels Of Severity
Each error message displayed by SQL Server has an associated error message number that uniquely identifies the type of error. The error severity levels provide a quick reference for you about the nature of the error. The error severity levels ARE between 1 and 24; it represents information about the source that issued the error. The error message is a description of the error that occurred. The error messages are stored in the sysmessages system table.
To see all the system messages running following query
select * from sysmessages
The severity level are displayed in the table below..
|
0
to 10
|
Messages with a severity level of
0 to 10 are informational messages and not actual errors.
|
|
11
to 16
|
Generated as a result of user
problems and can be fixed by the user. For
example, the error message returned in the invalid update query.
|
|
17
|
Indicates that SQL Server has run
out of a configurable resource, such as locks. Severity
error 17 can be corrected by the DBA, and in some cases, by the database
owner.
|
|
18
|
Indicate nonfatal internal
software problems.
|
|
19
|
Indicates that a no configurable
resource limit has been exceeded.
|
|
20
|
Indicates a problem with a
statement issued by the current process.
|
|
21
|
Indicates that SQL Server has
encountered a problem that affects all the processes in a database.
|
|
22
|
Means a table or index has been
damaged. To try to determine the extent of the problem, stop and restart SQL
Server. If the problem is in the cache and
not on the disk, the restart corrects the problem. Otherwise, use DBCC to
determine the extent of the damage and the required action to take.
|
|
23
|
Indicates a suspect database. To
determine the extent of the damage and the proper action to take, use the
DBCC commands.
|
|
24
|
Indicates a hardware problem.
|
Post a Comment