Skip to content

Instantly share code, notes, and snippets.

@jzabroski

jzabroski/SQL Mail - Update Mail Server

Created August 23, 2017 15:03
Show Gist options
  • Save jzabroski/3bc2a6dedf1ee4ead0c86f607aa5390b to your computer and use it in GitHub Desktop.
Save jzabroski/3bc2a6dedf1ee4ead0c86f607aa5390b to your computer and use it in GitHub Desktop.
Download ZIP
Raw
SQL Mail - Update Mail Server
-- Taken from: http://sqlblog.com/blogs/hugo_kornelis/archive/2014/07/18/database-mail-and-then-the-smtp-server-changed.aspx
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment