Change SMTP server name

                   Simple Mail Transfer Protocol or SMTP is what allows you to receive email alerts from your Instance to either yourself, your alerting system or group of people. Now, in order for SMTP to work, it must be configured with a server. Some of our Instances were still using old deprecated SMTP servers so I needed to update all the SMTP servers to the current one. I used the following script to resolve the issue across my multiple servers. FYI this script was executed via my CMS (central management server). If you have CMS  in your environment then great, if you do not then you would unfortunately have to execute this on each instance. 

USE msdb

GO

DECLARE @NewServer sysname=‘outlook.mydomain.com’,–NEW SMTP SERVER

@OldServer Varchar(30)

SELECT @oldserver=SERVERNAME

FROM msdb.dbo.sysmail_server

Where servername IN (‘111.111.1.11′,’10.22.2.22′,’10.33.3.33′,’olddie.mydomain.com’,’stillolddie.mydomain.com’) –OLD SMTP SERVER

DECLARE @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

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;

—In order to confirm that the change took effect, just run the below statement

SELECT SERVERNAME, @@servername

FROM msdb.dbo.sysmail_server;

 

By Dummea Vincent

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s