Using SQL Data Compare to Synchronize Custom Error Messages
Using SQL Data Compare to Synchronize Custom Error Messages
I present a trick to get SQL Data Compare to synchronize custom error messages, stored in the sys.messages system catalog view, across SQL Server instances.
Join the DZone community and get the full member experience.Join For Free
Read the 2019 State of Database DevOps Report for the very latest insights
One way to offer a better experience to our application users is to anticipate likely mistakes they could make, such as when filling in a web form, and to provide them with a meaningful error message that allows them to continue quickly with their work. One way to do this in SQL Server to is to use the
sp_addmessage system stored procedure, to add custom error messages to the
master.sys.messages system catalog view (which can then be referenced in our error handling routines, either using
RAISERROR), or, preferably,
To ensure consistent behavior, the administrator needs to "synchronize" the contents of
sys.messages across all SQL Server instances. Unfortunately, there's no easy, built-in way to do this. I wrote an article on SQLServerCentral.com that showed one technique, using simple string concatenation to capture custom messages from one instance and then a cursor that called
sp_addmessage to add them to
sys.messages on another instance. However, it would still be a cumbersome and tedious task for an administrator to make sure that each message is added to all relevant SQL Server instances, and for all languages in which you need to display the messages.
Surely, there must be an easier way. SQL Data Compare is designed specifically to synchronize data between source and target tables. However, we need a workaround to make SQL Data Compare work when comparing system views, which involves creating a proxy view, and some
INSTEADOF triggers on the view, in the
master database (a practice of which I generally disapprove).
This trick might be useful for small data sets and periodic synchronization, such as to ensure that high availability or disaster recovery instances that are not clustered have their custom error messages synched, but I wouldn't recommend it as a general-purpose technique, for other types of tables and views.
Using SQL Data Compare With a "Proxy" View
SQL Data Compare will the source database (or scripts folder) to the target and generate a script to synchronize the data in the two. When you deploy the script to the target, it will
INSERT any rows that exist in the source but not the target,
DELETE any that are in the target but not the source, and
UPDATE any that are in both, but different, to match what is in the source.
It's a perfect tool for synchronizing small volumes of static data, including reference data, seed data, test data, and custom error messages, which a developer or administrator needs to ensure are deployed to various downstream environments. However, the first problem we have is that the tool can't compare system views and tables, at least not directly.
The way around this is to create a "proxy" view of
sys.messages, in the
master database. You need to do this on both the source and target instances. This is a bit unorthodox, and I don't like creating objects in
master. However, it will work, and credit goes to Sam Blackburn, one of my colleagues at Redgate Software, who came up with this as a workaround.
To try this out, first, let's put a test message in the source database
EXEC sys.sp_addmessage @msgnum = 50001, @severity = 10, @msgtext = N' This is the lowest custom error message', @lang = 'us_english', @with_log = 'TRUE';
Now create the proxy view in the
master on the source instance, plus another copy of the view (empty) in
master on the target.
CREATE VIEW SysMessagesProxy AS 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;
Open SQL Data Compare. In this case, I'm comparing a SQL Server 2014 and SQL Server 2016 instance. On the Data Sources tab, set the source and target instances, with the Database as
master in each case.
On the Options tab, under Mapping Behavior, make sure the Include views checkbox is ticked. Now click over to the Tables&Views tab, where you'll need to specify the Comparison Key for our
SysMessagesProxy view. I'll use the
language_id columns. These will produce a unique key for the messages.
I've also set a
WHERE clause to ensure I'm only trying to compare user-defined (not system) error messages.
We can now run the comparison. SQL Data Compare will generate a script to insert the row into the target view but if you try to deploy it, it will fail because direct inserts and updates are not allowed on system tables.
We need one more "workaround" step.
Using an INSTEAD OF Trigger on the Target View
We need to create three
INSTEADOF triggers on the
SysMessageProxy view, one each to handle
UPDATE actions. So, for example, an attempt to
INSERT a row into the target view will fire the
AddCustomMessage trigger, and using an embedded cursor, it will iterate through each message in the
Inserted table. For each row, we first check to see if the message exists in
sys.messages. If so, we move to the next row.
If the row does not exist, we get the name of the language, corresponding to the row's language ID, from
sys.syslanguages. This is because the ID is stored in the table, but we need the name of the language for
sp_addmessage. The trigger then executes
sp_addmessage to insert the row into
sys.messages and then fetches the next row, and so on.
Listing 3 shows the code for the three triggers.
CREATE OR ALTER TRIGGER AddCustomMessage ON SysMessagesProxy INSTEAD OF INSERT AS BEGIN DECLARE @message_id INT, @language_id INT, @severity INT, @text NVARCHAR(MAX); DECLARE cur CURSOR FOR SELECT message_id, language_id, severity, text FROM Inserted; OPEN cur; FETCH NEXT FROM cur INTO @message_id, @language_id, @severity, @text; WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS (SELECT * FROM sys.messages WHERE message_id = @message_id) BEGIN DECLARE @languageName NVARCHAR(MAX) = (SELECT NAME FROM sys.syslanguages WHERE langid = @language_id); EXEC sp_addmessage @message_id, @severity, @text, @languageName; END FETCH NEXT FROM cur INTO @message_id, @language_id, @severity, @text; END; CLOSE cur; DEALLOCATE cur; END; GO CREATE OR ALTER TRIGGER DeleteSysMessage ON SysMessagesProxy INSTEAD OF DELETE AS BEGIN DECLARE @message_id INT, @language_id INT, @severity INT, @text NVARCHAR(MAX), @language sysname; DECLARE cur CURSOR FOR SELECT Deleted.message_id, Deleted.language_id, Deleted.severity, Deleted.text FROM Deleted; OPEN cur; FETCH NEXT FROM cur INTO @message_id, @language_id, @severity, @text; WHILE @@Fetch_Status = 0 BEGIN SELECT @language = s.name FROM sys.syslanguages AS s WHERE s.lcid = @language_id EXEC sys.sp_dropmessage @message_id, @language; FETCH NEXT FROM cur INTO @message_id, @language_id, @severity, @text; END; CLOSE cur; DEALLOCATE cur; END; GO CREATE OR ALTER TRIGGER UpdateSysMEssage ON SysMessagesProxy INSTEAD OF UPDATE AS BEGIN DECLARE @message_id INT, @language_id INT, @severity INT, @text NVARCHAR(MAX); DECLARE cur CURSOR FOR SELECT Inserted.message_id, Inserted.language_id, Inserted.severity, Inserted.text FROM Inserted; OPEN cur; FETCH NEXT FROM cur INTO @message_id, @language_id, @severity, @text; WHILE @@Fetch_Status = 0 BEGIN DECLARE @languageName NVARCHAR(MAX) = (SELECT syslanguages.name FROM sys.syslanguages WHERE syslanguages.langid = @language_id); EXEC sys.sp_addmessage @message_id, @severity, @text, @languageName, @text; FETCH NEXT FROM cur INTO @message_id, @language_id, @severity, @text; END; CLOSE cur; DEALLOCATE cur; END; GO
Now, we're ready to re-run the comparison project. I've added a few more custom messages to
SysMessageProxy on the source SQL Server 2014 instance, and one to the target SQL Server 2016 instance. Figure 4 shows the results of the comparison, using SQL Data Compare.
Click the Deploy button and choose Deploy using Data Compare. Upon clicking Next, you'll see the deployment script, which will be saved. I can see this has one
DELETE and four
Click Deploy, and you'll see a confirmation dialog that you're about to make a permanent change to the target; once this is deployed, you can't undo the changes. If you haven't captured the previous state of the data, you won't be able to easily go back.
Now, with our triggers in place, the deployment will work. Assuming you had the Recompare after deployment box checked, SQL Data Compare will re-run the comparison is run and you will see that the data is now in sync.Figure 6
I've presented a trick to get SQL Data Compare to synchronize custom error messages, stored in the
sys.messages system catalog view, across SQL Server instances. This can be a handy way of ensuring your DR and HA instances keep their messages in sync. That said, the idea of creating object in master will not be appealing to most administrators. Also, a trigger running a cursor is very inefficient and could cause resource and performance issues if used for large datasets, so I would not recommend this technique for other types of tables.
Published at DZone with permission of Steve Jones , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.