Migrating Custom Error Messages Between Servers

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

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