Migrating Custom Error Messages Between Servers

Over the past few years, I’ve helped a number of clients migrate their SQL Server workloads from one server to another. In many cases (especially for SMBs, i.e. small to medium businesses) this has involved migrating databases, logins, settings, jobs, etc. across servers hosted with different hosting companies—which is primarily where the source of this post is rooted.

It's also worthwhile to point out that I’m primarily writing this blog post for myself—as a way to ‘log’ some info about migrating custom error messages in the future in the case that I ever need to do so again. And, I point that out because (up until recently) while I’ve probably helped move around just shy of a hundred servers now, it wasn’t until just recently that I actually found the need to even move custom error messages. And that, in turn, is because this was the first time I’ve ever actually bumped into someone using them. (Which, in some ways seems amazing – but, then again, not really.)

Custom Errors in General

Of course, that might beg the question: “what’s a custom error”. And the answer to that is simple – if you think in terms of T-SQL’s RAISERROR statement – or its newer (but not 100% better) replacement in the form of the THROW statement—as both allow developers or SQL Server users to throw custom error messages.

Almost universally, I’ve seen these custom errors raised/thrown as follows:

  1. RAISERROR('This is a custom error!',12,1);
  2. GO
  4. THROW 50001, 'This is a custom error too!', 1;
  5. GO

Or, more specifically—where the actual error text is provided each and every time by the developer writing up whatever code is throwing the exception or error.

Which, of course, could get very lame/repetitive (think DRY) in some applications—especially if you were trying to do any types of validation or commonly throwing very similar, custom, errors. As such, there’s an option within SQL Server to add your OWN error messages into the server’s sys.messages table—via sp_addmessage. (And, note that if you’re going to look into using these in conjunction with THROW, then you’ll likely want to look into FORMATMESSAGE() as well.)

Migrating Custom Errors

So, given that it can be beneficial to create your own custom error messages, the reality is that you might bump into the need to obviously migrate them from one server to the next.

One option for doing so would be to use the Transfer Error Messages Task from within SQL Server Integration Services. Then again, some people also think that poking themselves in the eye with a sharp stick is fun too. Er, no, wait: I’m being sarcastic. Or, more specifically: while you could do that it’s a) potentially going to be a tiny bit of a pain in the butt as you’ll have to create a new SSIS package, specify a source connection and a destination connection, walk through the wizard and set all config options as needed and then b) hope that it works. Because, in my experience many of the SSIS ‘transfer’ tasks can be a bit of a beast to make work. And that’s going to be ESPECIALLY true if you’re migrating these messages from one data center to the next where you don’t have SMB/Kerberos connectivity between boxes.

As such—and for anyone astute enough to note/catch that I mentioned that sp_addmessage drops these custom error messages into sys.messages—another (and I think easier) option is simply to ‘script’ out any custom error messages on your source server and then copy/paste them over to the destination server—which is what the following script does.

  1. -- Don't forget to run CTRL+T on the source server.
  2. --              then you might want to quickly review any
  3. --              custom error messages output by this script
  4. --              before migrating to destination server.
  6. GO
  8. DECLARE @crlf char(2);
  9. DECLARE @tab char(1);
  10. SET @crlf = CHAR(13) + CHAR(10);
  11. SET @tab = CHAR(9);
  13. SELECT
  14.         'EXEC sp_addmessage ' + @crlf + @tab
  15.         + '@msgnum = ' + CAST(m.message_id AS varchar(30))
  16.                 + ', ' + @crlf + @tab
  17.         + '@severity = ' + CAST(m.severity AS varchar(3))  
  18.                 + ', ' + @crlf + @tab
  19.         + '@msgtext = ''' + REPLACE(m.[text],'''','''''')  
  20.                 + ''''  + ', ' + @crlf + @tab
  21.         + '@lang = ''' +
  22.         (SELECT TOP 1 name
  23.                 FROM master.sys.syslanguages l
  24.                 WHERE l.lcid = m.language_id)
  25.                 + ''', ' + @crlf + @tab
  26.         + '@with_log = ''' +
  27.         CASE WHEN m.is_event_logged = 1
  28.                 THEN 'TRUE' ELSE 'FALSE' END  + '''; '
  29.         -- Uncomment ONLY if you want to replace:
  30.         --+ '@replace = ''replace'';'
  31.         + @crlf + 'GO' + @crlf + @crlf
  32. FROM
  33.         master.sys.messages m
  34. WHERE
  35.         m.message_id > 50000;
  36. GO

And, note that the script reminds you that’ll want to ‘kick’ the output of this script out in ‘text mode’ by either selecting CTRL+T (for text) or selecting the Query > Results To > Results to Text menu option. 

Discuss this Blog Entry 1

on May 30, 2014

VERY helpful, thanks! Here's a version to run on SQL 2000:

-- Don't forget to run CTRL+T on the source server.
-- then you might want to quickly review any
-- custom error messages output by this script
-- before migrating to destination server.

DECLARE @crlf char(2);
DECLARE @tab char(1);
SET @crlf = CHAR(13) + CHAR(10);
SET @tab = CHAR(9);

'EXEC sp_addmessage ' + @crlf + @tab
+ '@msgnum = ' + CAST(m.error AS varchar(30))
+ ', ' + @crlf + @tab
+ '@severity = ' + CAST(m.severity AS varchar(3))
+ ', ' + @crlf + @tab
+ '@msgtext = ''' + REPLACE(m.[description],'''','''''')
+ '''' + ', ' + @crlf + @tab
+ '@lang = ''' +
(SELECT TOP 1 name
FROM master.dbo.syslanguages l
WHERE l.lcid = m.msglangid)
+ ''', ' + @crlf + @tab
+ '@with_log = ''' +
CASE WHEN m.dlevel = 128
-- Uncomment ONLY if you want to replace:
--+ '@replace = ''replace'';'
+ @crlf + 'GO' + @crlf + @crlf
master.dbo.sysmessages m
m.error > 50000;

Please or Register to post comments.

What's Practical SQL Server?

Practical advice, insight, and help for core SQL Server considerations.


Michael K. Campbell

Michael K. Campbell is a contributing editor for SQL Server Pro and Dev Pro and is an ASPInsider. Michael is the president of OverAchiever Productions, a consultancy dedicated to technical evangelism...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×