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:

RAISERROR('This is a custom error!',12,1);
GO

THROW 50001, 'This is a custom error too!', 1;
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.

-- 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.message_id AS varchar(30)) 
		+ ', ' + @crlf + @tab
	+ '@severity = ' + CAST(m.severity AS varchar(3))  
		+ ', ' + @crlf + @tab
	+ '@msgtext = ''' + REPLACE(m.[text],'''','''''')  
		+ ''''  + ', ' + @crlf + @tab
	+ '@lang = ''' + 
	(SELECT TOP 1 name 
		FROM master.sys.syslanguages l 
		WHERE l.lcid = m.language_id) 
		+ ''', ' + @crlf + @tab
	+ '@with_log = ''' + 
	CASE WHEN m.is_event_logged = 1 
		THEN 'TRUE' ELSE 'FALSE' END  + '''; '
	-- Uncomment ONLY if you want to replace:
	--+ '@replace = ''replace'';' 
	+ @crlf + 'GO' + @crlf + @crlf 
FROM 
	master.sys.messages m
WHERE 
	m.message_id > 50000;
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.

Comments

Plain text