You can read
it from…
Error
handling
In this article I am trying to illustrate the
point related to custom error messages.
Custom error
messages allow us to design more business specific error messages. Each custom
error message has a severity assignment, which determines how important the
error is and identifies how it should be handled. Some error messages are
simply informational and are not even captured by error handling. Other
error messages are very severe and immediately kill the process on which the
statement was executed.
Defining
custom error messages
To define a
custom error message in SQL Server 2005, we can use the stored procedure
named sp_addmessage, which adds a record to the sys.messages system view.
The syntax is mentioned bellow
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. msg_id is int with
a default of NULL. msg_id for user-defined error messages can
be an integer between 50,001 and 2,147,483,647.
[ @severity
= ] severity
Is the severity level of the error. severity is smallint with
a default of NULL. Valid levels are from 1 through 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'
If
specified as the string replace, an existing error message is overwritten
with new message text and severity level. replace is varchar(7) with
a default of NULL. This option must be specified if msg_id already
exists. If you replace a U.S. English message, the severity level is replaced
for all messages in all other languages that have the same msg_id.
To execute this
stored procedure, we need to provide an error number which will start from
50,001 to 2,147,483,647 for
user-defined messages.
Example
Sample-1
USE master
GO
EXEC sp_addmessage
50001,
1,
N'This
message is is the Sample Error Message';
Sample-2
EXEC sp_addmessage
50002,
16,
N'This
actually causes an error, and is caught by error-handling';
Sample-3
EXEC sp_addmessage
50003,
20,
N'This
causes an error, and stops any further processing. This is not caught by error handling.';
Calling
Custom Error Messages
Now that my custom error messages are defined, we can use
them inside our database engine. To invoke these errors, we’ll use the
RAISERROR T-SQL construct.
RAISERROR accepts an error number, a severity level, and
a state number.
Example
Sample-1
BEGIN
BEGIN
TRY
RAISERROR (50001,1,1) WITH LOG
END
TRY
BEGIN
CATCH
SELECT
ERROR_MESSAGE(), ERROR_NUMBER ()
END
CATCH
END
Result set:
This message is is the Sample
Error Message
Msg 50001, Level 1, State 1
Sample-2
This statement invokes the second custom error message I
define above. This message has a defined severity of 16, which will get caught
by my CATCH statement. These types of error messages are some of the more
commonly seen messages inside the SQL Server database engine.
BEGIN
BEGIN
TRY
RAISERROR (50002,16,1) WITH LOG
END
TRY
BEGIN
CATCH
SELECT
ERROR_MESSAGE(), ERROR_NUMBER ()
END
CATCH
END
Result set:
This
actually causes an error, and is caught by error-handling
|
50002
|
Sample-3
This final snippet calls the third custom message defined
above. Due to the severity level defined in this custom error, the CATCH block
is not invoked; in fact, the statement and connection is immediately terminated.
BEGIN
BEGIN
TRY
RAISERROR (50003, 20,1) WITH LOG
END
TRY
BEGIN
CATCH
SELECT ERROR_MESSAGE(), ERROR_NUMBER ()
END
CATCH
END
Result set:
Msg 2745, Level 16,
State 2, Line 3
Process ID 52 has
raised user error 50003, severity 20. SQL Server is terminating this process.
Msg 50003, Level 20,
State 1, Line 3
This causes an
error, and stops any further processing.
This is not caught by error handling.
Msg 0, Level 20,
State 0, Line 0
A severe error
occurred on the current command. The
results, if any, should be discarded.
To
Alter Error Message
Alters the state of user-defined messages in an instance
of the SQL Server Database Engine. User-defined messages can be viewed using
the sys.messages catalog view.
Syntax
sp_altermessage [ @message_id = ] message_number,
[ @parameter = ] 'write_to_log',
[ @parameter_value = ] 'value'
Arguments
[ @message_id = ] message_number
Is
the error number of the message to alter from sys.messages. message_number is int with
no default value.
[ @parameter = ] 'write_to_log'
Is
used with @parameter_value to indicate that the message is to be
written to the Microsoft Windows application log. write_to_log is sysname with
no default value.write_to_log must be set to WITH_LOG or NULL. If write_to_log is
set to WITH_LOG or NULL, and the value for @parameter_value is true,
the message is written to the Windows application log. If write_to_log is
set to WITH_LOG or NULL and the value for @parameter_value is false,
the message is not always written to the Windows application log, but may be
written depending upon how the error was raised. If write_to_log is
specified, the value for @parameter_value must also be specified.
[ @parameter_value = ] 'value'
Is
used with @parameter to indicate that the error is to be written to
the Microsoft Windows application log. value is varchar(5), with
no default value. 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 may be written depending upon how the error was raised. If value is
specified, write_to_log for @parameter must also be
specified
Returns
It returns 0 (Success) or 1 (Failure)
Example
sp_altermessage 55001, 'WITH_LOG', 'true';
GO
To
DROP a specified Error Message
Drops a specified user-defined error message from an
instance of the SQL Server Database Engine. User-defined messages can be viewed
using the sys.messages catalog view.
Syntax:
sp_dropmessage [ @msgnum = ] message_number
[ , [ @lang = ] 'language' ]
Arguments
[ @msgnum = ] message_number
Is
the message number to drop. message_number must be a user-defined
message that has a message number greater than 50000. message_number is int,
with a default of NULL.
[ @lang = ] 'language'
Is
the language of the message to drop. If all is specified, all
language versions of message_number are dropped. language is sysname,
with a default of NULL.
Example:
USE master;
GO
EXEC sp_dropmessage 50001;
Hope you like it.
Posted by: MR. JOYDEEP DAS
No comments:
Post a Comment