skreebydba

In my last post, I shared a script to automate the migration of SQL Server Database Mail settings. In this post, I show how to send test e-mails from all Database Mail profiles on an instance. The migration I was working on contained 21 Database Mail profiles. The following script will send a test e-mail from each profile to confirm successful configuration. I hope you can put this code to use in your migrations.

USE msdb;

SET NOCOUNT ON;

/* Drop and create temp table
   and declare local variables */
DROP TABLE IF EXISTS #profiles;

CREATE TABLE #profiles
(RowId INT IDENTITY(1,1)
,ProfileName SYSNAME);

/* Update @recipient 
   Set @noexec = 1 to print the EXEC sp_send_dbmail statements
   Set @noexec = 0 to execute the statements immediately */
DECLARE @profilename SYSNAME,
@sqlstr NVARCHAR(2000),
@loopcount INT = 1,
@looplimit INT,
@noexec INT = 1,
@recipient NVARCHAR(255) = N'fgill@concurrency.com';

/* Insert Database Mail profile names into temp table
   and initialize the loop limit variable */
INSERT INTO #profiles
(ProfileName)
SELECT name
FROM sysmail_profile;

SELECT @looplimit = MAX(RowId) FROM #profiles;

/* Loop through the list of Database Mail profiles and 
   execute sp_send_dbmail for each one */
WHILE @loopcount <= @looplimit
BEGIN

	SELECT @profilename = ProfileName FROM #profiles WHERE RowId = @loopcount;


	SELECT @sqlstr = CONCAT('EXEC msdb.dbo.sp_send_dbmail  
		@profile_name = ''',
	@profilename,''',  
		@recipients = ''', @recipient, ''',  
		@body = ''Database mail succeeded for ', @profilename, ' on SQLWEBDB-01.'',  
		@subject = ''Database mail test for ', @profilename, ';''');

	SELECT @loopcount += 1;

	IF @noexec = 1
	BEGIN

		PRINT @sqlstr;

	END
	ELSE
	BEGIN

		EXEC sp_executesql @sqlstr;

	END
		
END