November 15, 2017 at 10:03 pm
Comments posted to this topic are about the item Exporting Custom Messages from Sys.Messages
November 16, 2017 at 9:49 pm
I suppose that doing this in the following fashion
DECLARE @commandText VARCHAR(8000)=''
SELECT @commandText = @commandText +char(10) +char(13)+
'EXEC master.sys.sp_addmessage @msgnum = ' + CAST(message_id+10 AS VARCHAR(10)) + ', @severity = '
+ CAST(m.severity AS VARCHAR(10)) + ', @msgtext = ''' + m.text + '''' + ', @lang = ''' + s.name + ''''
+ ', @with_log = ''' + CASE
WHEN m.is_event_logged = 1 THEN
'True'
ELSE
'False'
END + '''; '
FROM sys.messages AS m
INNER JOIN sys.syslanguages AS s
ON m.language_id = s.lcid
WHERE m.message_id > 49999;
EXEC(@commandText);
Could be problematic if the text of messages together exceed the limit of the VARCHAR(8000) variable. I like your cursor approach there
----------------------------------------------------
November 17, 2017 at 10:42 am
True, plus it's an all or nothing. If there's some weird text you haven't handled, none of your items might get through. I like the separate items in that I can move messages as separate transactions. We can certainly check the counts later (or join tables) to determine if we've missed anything.
May 24, 2019 at 3:15 pm
The below should fix one glaring oversight in the code, which is handling single quotes/apostrophes in the message text. But luckily that is an easy code change using QUOTENAME().
DECLARE mycurs CURSOR FOR
SELECT m.message_id,
'EXEC master.sys.sp_addmessage @msgnum = ' + CAST(m.message_id AS VARCHAR(10)) + ', @severity = '
+ CAST(m.severity AS VARCHAR(10)) + ', @msgtext = ' + QUOTENAME(m.text, '''') + ', @lang = ''' + s.name + ''''
+ ', @with_log = ''' + CASE
WHEN m.is_event_logged = 1 THEN
'True'
ELSE
'False'
END + '''; '
FROM [PLATO\SQL2014].master.sys.messages AS m
INNER JOIN [PLATO\SQL2014].master.sys.syslanguages AS s
ON m.language_id = s.lcid
WHERE m.message_id > 49999;
OPEN mycurs;
DECLARE @msgid INT, @cmd VARCHAR(2000);
FETCH NEXT FROM mycurs
INTO @msgid, @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT * FROM sys.messages WHERE message_id = @msgid)
EXEC(@cmd);
FETCH NEXT FROM mycurs
INTO @msgid, @cmd;
END;
DEALLOCATE mycurs;
I also qualified message_id, but I did not change the cursor to readonly as SQL Prompt suggests nor did I declare it as forward only as SQL Prompt also suggested.
It's likely that instead of "EXEC(@cmd)" that "EXEC sys.sp_executesql @cmd" be used to reduce chance of SQL injection, but the addition of QUOTENAME() already reduced that threat. But using the sp_executesql method opens up the option to have @cmd not be dynamic SQL but have parameter names in the text then use the cursor to pass in the parameters through sp_executesql's parameter handling.
Maybe it could use some error handling but that's overkill for casual use of this sort of query and should only be needed if using it on a large scale and/or often.
Thank you for this post. I hadn't thought about using this sort of approach for this, I likely would have used a similar method to output the code as text in the result window then copy and paste it into another query window for use and/or save it to a file for later use.
May 24, 2019 at 5:54 pm
Thanks for the note. If I get a chance, i'll update the article. Of if you want to write an updated version that talks about the enhancements of sp_executesql over exec(), it would be great.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply