Change from and replyto e-mail address for DBMail ccount and test it

change-dbmail-account.sql

DECLARE	@old_email_address nvarchar(128) = N'[email protected]';
DECLARE	@new_email_address nvarchar(128) = N'[email protected]';
DECLARE	@old_replyto_address nvarchar(128) = N'[email protected]';
DECLARE	@new_replyto_address nvarchar(128) = N'[email protected]';
DECLARE	@test_email_address nvarchar(128) = N'[email protected]';

USE msdb;
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#tmp_account') IS NOT NULL
	DROP TABLE #tmp_account;

CREATE TABLE #tmp_account (
	account_id				int				NOT NULL PRIMARY KEY
,	account_name			sysname			NOT NULL
,	accound_description		nvarchar(256)	NULL
,	email_address			nvarchar(128)	NULL
,	display_name			nvarchar(128)	NULL
,	replyto_address			nvarchar(128)	NULL	
,	servertype				sysname			NOT NULL
,	servername				sysname			NOT NULL
,	[port]					int				NULL
,	username				nvarchar(128)	NULL
,	use_default_credentials	bit				NOT NULL
,	enable_ssl				bit				NOT NULL
);

INSERT	#tmp_account
EXEC msdb.dbo.sysmail_help_account_sp;

DECLARE	@cur_account_id			int;
DECLARE	@cur_email_address		nvarchar(128);
DECLARE	@cur_replyto_address	nvarchar(128);
DECLARE	@cur_profile_name		sysname;

WHILE EXISTS (SELECT * FROM #tmp_account WHERE email_address = @old_email_address OR replyto_address = @old_replyto_address)
BEGIN

	SELECT TOP 1
		@cur_account_id = account_id
	,	@cur_email_address = email_address
	,	@cur_replyto_address = replyto_address
	FROM	#tmp_account
	ORDER BY account_id;

	RAISERROR('Processing mail account id %d', 10, 1, @cur_account_id);

	IF @cur_email_address = @old_email_address SET @cur_email_address = @new_email_address;
	IF @cur_replyto_address = @old_replyto_address SET @cur_replyto_address = @new_replyto_address;

	EXEC msdb.dbo.sysmail_update_account_sp
		@account_id = @cur_account_id
	,	@email_address = @cur_email_address
	,	@replyto_address = @cur_replyto_address;

	DELETE	#tmp_account
	WHERE	account_id = @cur_account_id;

		IF OBJECT_ID('tempdb..#tmp_profileaccount') IS NOT NULL
		DROP TABLE #tmp_profileaccount;

	CREATE TABLE #tmp_profileaccount (
		profile_id		int				NOT NULL
	,	profile_name	sysname			NOT NULL
	,	account_id		int				NOT NULL
	,	account_name	sysname			NOT NULL
	,	sequence_number	int				NOT NULL
	,	PRIMARY KEY (account_id, profile_id)
	);
	INSERT	#tmp_profileaccount
	EXEC	msdb.dbo.sysmail_help_profileaccount_sp @account_id = @cur_account_id

	WHILE EXISTS (SELECT * FROM #tmp_profileaccount WHERE account_id = @cur_account_id)
	BEGIN 
		SELECT	@cur_profile_name = profile_name
		FROM	#tmp_profileaccount
		WHERE	account_id = @cur_account_id

		RAISERROR('Sending test mail account id %d profile name [%s]', 10, 1, @cur_account_id, @cur_profile_name);
		
		DECLARE	@test_subject nvarchar(max) = FORMATMESSAGE(
			N'Test mail from server %s profile %d account %d'
		,	@@SERVERNAME, @cur_account_id, @cur_account_id);

		EXEC	msdb.dbo.sp_send_dbmail
				@profile_name = @cur_profile_name
		,		@recipients = @test_email_address
		,		@subject = @test_subject
		,		@body = N'Just a test, please Ignore';
		
		DELETE	#tmp_profileaccount WHERE account_id = @cur_account_id;

		WAITFOR DELAY '00:00:01';
	END
	WAITFOR DELAY '00:00:01';
END

-- dump config
EXEC msdb.dbo.sysmail_help_account_sp;
SET NOCOUNT OFF;
GO

-- DEBUG
--EXECUTE msdb.dbo.sysmail_stop_sp;
--EXECUTE msdb.dbo.sysmail_help_status_sp;
--SELECT count(*) FROM msdb.dbo.sysmail_unsentitems
--EXECUTE msdb.dbo.sysmail_start_sp;
--EXECUTE msdb.dbo.sysmail_help_status_sp;