Update an User-Defined Error Message

 1
 2PRINT '------------------------------------'
 3PRINT '-- Script to update/add SysMessages'
 4PRINT '--'
 5PRINT '-- Date   :' + cast(GETDATE() as nvarchar(max))
 6PRINT '-- Source :' + @@servername
 7PRINT '------------------------------------'
 8
 9SET NOCOUNT ON;
10DECLARE @crlf CHAR(2);
11DECLARE @tab CHAR(1);
12DECLARE @sql NVARCHAR(MAX);
13DECLARE @sqlResult NVARCHAR(MAX);
14SET @crlf = CHAR(13) + CHAR(10);
15SET @tab = CHAR(9);
16
17SET @sql = '
18IF (EXISTS (
19    SELECT  M.message_id, M.language_id, M.severity, M.is_event_logged, M.text
20    FROM    sys.messages AS M INNER JOIN sys.syslanguages AS L ON M.language_id = L.lcid
21    WHERE     (M.message_id = [*msgnum*]) AND (M.text = [*msgtext*]) AND (L.name = [*lang*])
22))
23BEGIN
24    PRINT ''-- Message exists [*msgnum*] ('' + [*lang*] + '') : '' + [*msgtext*];
25END
26ELSE
27BEGIN
28    IF (EXISTS (
29    SELECT  M.message_id, M.language_id, M.severity, M.is_event_logged, M.text
30    FROM    sys.messages AS M INNER JOIN sys.syslanguages AS L ON M.language_id = L.lcid
31    WHERE     (M.message_id = [*msgnum*]) AND (L.name = [*lang*])
32    ))
33    BEGIN
34        PRINT ''-- Update message [*msgnum*] ('' + [*lang*] + '') : '' + [*msgtext*];
35        EXEC sp_dropmessage @msgnum = [*msgnum*], @lang = [*lang*];
36    END
37    PRINT ''-- Add new message [*msgnum*] ('' + [*lang*] + '') : '' + [*msgtext*];
38    EXEC sp_addmessage @msgnum = [*msgnum*], @severity = [*severity*], @msgtext = [*msgtext*], @lang = [*lang*], @with_log = [*with_log*];
39END;
40
41--  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --
42';
43
44DECLARE @msgnum NVARCHAR(50);
45DECLARE @severity NVARCHAR(50);
46DECLARE @msgtext NVARCHAR(2048)
47DECLARE @lang NVARCHAR(50);
48DECLARE @with_log NVARCHAR(50);
49
50BEGIN TRY drop table #message; END TRY BEGIN CATCH END CATCH;
51SELECT M.message_id AS msgnum
52    ,M.severity
53    ,M.TEXT AS msgtext
54    ,L.NAME AS lang
55    ,CASE WHEN M.is_event_logged = 1 THEN 'TRUE' ELSE 'FALSE' END AS with_log
56INTO [#message]
57FROM sys.messages AS M
58INNER JOIN sys.syslanguages AS L ON M.language_id = L.lcid
59WHERE (M.message_id > 50000);
60WHILE (exists(select * from #message))
61BEGIN
62
63    select TOP 1 @msgnum = msgnum, @severity = severity, @msgtext = msgtext, @lang = lang, @with_log = with_log from #message
64    Delete from #message where @msgnum = msgnum AND @lang = lang;
65
66    SET @sqlResult = @sql;
67    SET @sqlResult = REPLACE(@sqlResult, '[*msgnum*]', @msgnum);
68    SET @sqlResult = REPLACE(@sqlResult, '[*severity*]', @severity);
69    SET @msgtext = ''''+REPLACE(@msgtext, '''', '''''''')+'''';
70    SET @sqlResult = REPLACE(@sqlResult, '[*msgtext*]', @msgtext);
71    SET @lang = ''''+REPLACE(@lang, '''', '''''''')+'''';
72    SET @sqlResult = REPLACE(@sqlResult, '[*lang*]', @lang);
73    SET @with_log = ''''+REPLACE(@with_log, '''', '''''''')+'''';
74    SET @sqlResult = REPLACE(@sqlResult, '[*with_log*]', @with_log);
75    PRINT @sqlResult;
76
77END