Migrating Custom Error Messages Between Servers

Discuss this Blog Entry 2

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.
SET NOCOUNT ON;
GO

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

SELECT
'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
THEN 'TRUE' ELSE 'FALSE' END + '''; '
-- Uncomment ONLY if you want to replace:
--+ '@replace = ''replace'';'
+ @crlf + 'GO' + @crlf + @crlf
FROM
master.dbo.sysmessages m
WHERE
m.error > 50000;
GO

on Sep 29, 2015

Thanks for a great script! FYI - I reviewed the output in Text mode but had to switch back to Grid mode because otherwise some of the longer error messages were getting cut off. I tried increasing the number of characters in Options but that didn't seem to work.

Please or Register to post comments.

What's Practical SQL Server?

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

Contributors

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) ×