One of the ways that many people provide better error handling in their application is by using custom error messages that can be returned when issues arise. For a SQL Server, these custom messages are stored inside of master.sys.messages and added with sp_addmessage. This can be a cumbersome and tedious task for administrators to be sure that each message is added to all relevant SQL Server instances and for all languages.

There isn't an easy way to migrate messages between instances that is built into SQL Server. Sys.messages is a system table, so direct inserts and updates are not allowed. This article will discuss one way that administrators might find an easy way to move messages programmatically from one instance to another.

Finding Custom Messages

The first step in migrating messages is finding those that exist in your instance. This query will find the custom error messages by querying the DMV.

SELECT m.message_id,
       m.language_id,
       m.severity,
       m.is_event_logged,
       m.text
FROM sys.messages AS m
WHERE m.message_id > 50000;

These are the values that have been added by a sysadmin or serveradmin in SQL Server. Let's test that. I'll add a custom message to my local instance with this code:

EXEC sys.sp_addmessage @msgnum = 50001,
                       @severity = 10,
                       @msgtext = N'This is the lowest custom error message',
                       @lang = 'us_english',
                       @with_log = 'TRUE'

When I run the query above, I'll get this back:

message_id  language_id severity is_event_logged text
----------- ----------- -------- --------------- -------------------------------------------
50001       1033        10       1               This is the lowest custom error message 
(1 row affected)

This show my message exists. If I had other messages, they'd be returned as well.

Migrating Messages

If I could copy this to the sys.messages DMV on another instance, I could setup a linked server and use an INSERT..SELECT through the linked server to move the data. However, I can't update system tables or views. Also, I need to use sp_addmessage as well, which only inserts single messages at a time.

One way to easily move messages is to build a series of EXEC sp_addmessage queries that can be executed. Let's do that by starting with the format of sp_addmessage. This procedure takes six parameters and they are:

• @msgnum
• @severity
• @msgtext
• @lang
• @with_log
• @replace

The first five of these need data that we can obtain from the DMV. The last one determines if we update existing messages with the values passed in. We won't be dealing with existing messages, but just those that don't exist.

One method I've used to build migration statements in the past is with string concatenation. I can do that by setting an sp_addmessage text with parameters in my column list, adding concatenation with the results from my query. This gives me the following query:

SELECT 'EXEC master.sys.sp_addmessage @msgnum = ' + CAST(message_id AS VARCHAR(10)) + ', @severity = '
       + CAST(m.severity AS VARCHAR(10)) + ', @msgtext = ''' + m.text + '''' + ', @lang = ''' + s.name + ''''
       + ', @with_log = ''' + CASE
                                  WHEN m.is_event_logged = 1 THEN
                                      'True'
                                  ELSE
                                      'False'
                              END + ''''
FROM sys.messages AS m
    INNER JOIN sys.syslanguages AS s
        ON m.language_id = s.lcid
WHERE m.message_id > 49999;

When I run this (after adding a two more messages, I get this output:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXEC master.sys.sp_addmessage @msgnum = 50001, @severity = 10, @msgtext = 'This is the lowest custom error message', @lang = 'us_english', @with_log = 'True'
EXEC master.sys.sp_addmessage @msgnum = 50002, @severity = 10, @msgtext = 'A customer name must be provided.', @lang = 'us_english', @with_log = 'True'
EXEC master.sys.sp_addmessage @msgnum = 50003, @severity = 10, @msgtext = 'A new line item cannot be added without an order being created.', @lang = 'us_english', @with_log = 'True'
(3 rows affected)

If I reformat the first line (with SQL Prompt), I have this code:

EXEC master.sys.sp_addmessage @msgnum = 50001,
                              @severity = 10,
                              @msgtext = 'This is the lowest custom error message',
                              @lang = 'us_english',
                              @with_log = 'True';

This is exactly the code that I need to add one message. If you need the replace parameter, you can add this, but you will need logic that also checks for existing messages and handles the sp_addmessage statement.

Adding Multiple Messages

In order to add multiple messages, I need to execute each row of my result set. There are numerous ways to do this, but here's a simple way. Since this is a rare event, not something that lots of logins will run, I can easily do this as a cursor. Witha linked server on another instance, I can run this code:

DECLARE mycurs CURSOR FOR
SELECT m.message_id, 
       'EXEC master.sys.sp_addmessage @msgnum = ' + CAST(message_id AS VARCHAR(10)) + ', @severity = '
       + CAST(m.severity AS VARCHAR(10)) + ', @msgtext = ''' + m.text + '''' + ', @lang = ''' + s.name + ''''
       + ', @with_log = ''' + CASE
                                  WHEN m.is_event_logged = 1 THEN
                                      'True'
                                  ELSE
                                      'False'
                              END + '''; '
FROM [PLATO\SQL2014].master.sys.messages AS m
    INNER JOIN [PLATO\SQL2014].master.sys.syslanguages AS s
        ON m.language_id = s.lcid
WHERE m.message_id > 49999;
OPEN mycurs;
DECLARE @msgid INT, @cmd VARCHAR(2000);
FETCH NEXT FROM mycurs
INTO @msgid, @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF NOT EXISTS (SELECT * FROM sys.messages WHERE message_id = @msgid)
    EXEC(@cmd);
    FETCH NEXT FROM mycurs
    INTO @msgid, @cmd;
END;
DEALLOCATE mycurs;

This queries my first instance and then executes each row as a batch on my current instance. All my messages get moved in this way.

There are certainly other ways you could process the results and submit them to another instance, perhaps with PowerShell or another scripting mechanism, but this is simple enough for me.

Summary

Migrating custom messages from one instance to another isn't hard, but the process isn't as simple as it could be. We don't have firts class DDL/DML to do this, so we need some sort of mechanism to execute sp_addmessage. This article shows how a cursor can be used to easily move messages.

This doesn't cover removing messages, but the technique presented here could be used with sp_dropmessage to remove existing messages that are on the target server.