Archive
Archive for November 13, 2017
Validate SQL Server Database Mail Settings
November 13, 2017
1 comment
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
Categories: SQL Server, SQL Server Database Mail, T-SQL
SQL Server, SQL Server Agent, T-SQL