THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

Database Mail … and then the SMTP Server changed

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.

Published Friday, July 18, 2014 11:30 PM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Argenis said:

This is why DNS CNAMEs are so important.

July 21, 2014 4:05 PM

Leave a Comment

(required) 
(required) 
Submit

About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement