The database I inherited when I started my current job sends out lots of mails. It does so using Database Mail, and it has lots of mail accounts and mail profiles defined. I know that several of those profiles are used, I suspect that some are not, and I have no idea about the rest – one day I will find the time to clean up, but so far there have always been more pressing matters to attend to.
But today the mail administrator told me that due to a change in architecture, SQL Server had to start using a different SMTP server for sending mails. Quite an easy task if you have just a single profile – just a few clicks in the Database Mail Configuration Wizard, and done. But repeating those same mouse-clicks for every profile in the list was not my idea of a morning well spent, so I decided that I’d have to script this. (This should be easy – we have just a single SMTP server, so I could hit every single mail account and did not have to bother with exceptions).
Usually, scripts for such a task are very easy – just type a well-chosen search string in your favorite search engine, check the first two or three hits, and you’ll have a script. Usually even more than one. Carefully inspect the script (just because it’s on the internet does not mean it’s safe!), copy the script, paste into SSMS, make adjustments for your own situation, do one more inspection just to be sure – and then hit the F5 button and say “time to grab a coff … oh wait, it’s already done”.
In this case I had no luck. Maybe I used the wrong search phrase, or maybe there is a way to accomplish this that is so easy that nobody ever bother blogging about it and I am the only one who managed to overlook the option. Or maybe nobody has ever tried to automate the task of changing SMTP servers.
Bottom line, I did not find a pre-made script for this task, so I put in the effort to write one, and then decided to share it with you.
Note that the script below was tested on SQL Server 2008R2 only. Also note that it will update all mail accounts to use the new SMTP server. If you have a more complex setup with multiple servers and only some need to change, you will have to add the correct filtering criteria to the WHERE clause.
DECLARE @NewServer sysname = 'NotTelling.mail', -- New SMTP server
@OldServer sysname = 'MySecret.mail', -- Old SMTP server
@account_id int;
DECLARE Cursor_MailAccounts CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR SELECT account_id
FROM msdb.dbo.sysmail_server
WHERE servername = @OldServer; -- Add extra logic here
OPEN Cursor_MailAccounts;
FETCH NEXT
FROM Cursor_MailAccounts
INTO @account_id;
WHILE @@FETCH_STATUS = 0
BEGIN;
EXECUTE msdb.dbo.sysmail_update_account_sp
@account_id = @account_id,
@mailserver_name = @NewServer;
FETCH NEXT
FROM Cursor_MailAccounts
INTO @account_id;
END;
CLOSE Cursor_MailAccounts;
DEALLOCATE Cursor_MailAccounts;
(And remember, just because you found it on the internet doesn’t mean it’s safe!)
With that done, my next task was to fix the mails being sent from SSIS packages. They use an SMTP connection that is defined in the package, not SQL Server’s own Database Mail, so I had to open and edit them by hand. Luckily, I have only four packages active, and only three of them have a Send Mail task, so I did not see any need to automate this.
2 Comments. Leave new
This is why DNS CNAMEs are so important.
If you had more than a few SSIS packages, remembering that they’re just XML files, once you figure out the difference from changing one package, you can change them all with a global search/replace. Notepad++ is handy for tasks like this but there are plenty of alternatives.
Oh, I wouldn’t do that if my packages were not source controlled, since that gives you an easy way to check the diffs and to backout the changes altogether if (when?) you mess up.