Pages

Google Analytics Alternative

Search This Blog

Thursday, November 24, 2011

sp_addmessage can be used to create a User-Defined Error Message

hi friends,
On Today Morning i go through with Custom Error Message in MSSql server.
for that i work around below step and it's done man.

i found one stored procedure "sp_addmessage" which used for adding my custome error message on server..



USE master
GO
EXEC sp_addmessage
50002,
10,
N'The data %s already exists!'
GO
RAISERROR (50002, 10, 1, 'MyText')


syntax for sp_addmessage is

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


[ @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. The combination of msg_id and language must be unique; an error is returned if the ID already exists for the specified language.

[ @severity = ]severity
Is the severity level of the error. severity is smallint with a default of NULL. Valid levels are from 1 through 25. For more information about severities, see Database Engine Error Severities.

[ @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.

 Note
If a message is written to the Windows application log, it is also written to the Database Engine error log file.

[ @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.

Examples :

A. Defining a custom message

The following example adds a custom message to sys.messages.
USE master
GO
EXEC sp_addmessage 50001, 16, 
   N'Percentage expects a value between 20 and 100. 
   Please reexecute with a more appropriate value.';
GO

B. Adding a message in two languages

The following example first adds a message in U.S. English and then adds the same message in French.
USE master;
GO
EXEC sp_addmessage @msgnum = 60000, @severity = 16, 
   @msgtext = N'The item named %s already exists in %s.', 
   @lang = 'us_english';

EXEC sp_addmessage @msgnum = 60000, @severity = 16, 
   @msgtext = N'L''élément nommé %1! existe déjà dans %2!', 
   @lang = 'French';
GO

C. Changing the order of parameters

The following example first adds a message in U.S. English, and then adds a localized message in which the parameter order is changed.
USE master;
GO

EXEC sp_addmessage 
    @msgnum = 60000, 
    @severity = 16,
    @msgtext = 
        N'This is a test message with one numeric
        parameter (%d), one string parameter (%s), 
        and another string parameter (%s).',
    @lang = 'us_english';

EXEC sp_addmessage 
    @msgnum = 60000, 
    @severity = 16,
    @msgtext = 
        -- In the localized version of the message,
        -- the parameter order has changed. The 
        -- string parameters are first and second
        -- place in the message, and the numeric 
        -- parameter is third place.
        N'Dies ist eine Testmeldung mit einem 
        Zeichenfolgenparameter (%3!),
        einem weiteren Zeichenfolgenparameter (%2!), 
        und einem numerischen Parameter (%1!).',
    @lang = 'German';
GO  

-- Changing the session language to use the U.S. English
-- version of the error message.
SET LANGUAGE us_english;
GO

RAISERROR(60000,1,1,15,'param1','param2') -- error, severity, state,
GO                                       -- parameters.

-- Changing the session language to use the German
-- version of the error message.
SET LANGUAGE German;
GO

RAISERROR(60000,1,1,15,'param1','param2') -- error, severity, state, 
GO


FellFree add your comment to make this blog more useful.

Thanks




No comments: