I use this code to validate Database Mail profile configuration:
/*
Confirm the Database Mail account and profile is configured correctly
*/
DECLARE @ProfileName varchar(255);
DECLARE @AccountName varchar(255);
DECLARE @AccountFromAddress varchar(255);
DECLARE @AccountReplyTo varchar(255);
DECLARE @SMTPServer varchar(255);
DECLARE @SMTPPort varchar(255);
DECLARE @DatabaseMail varchar(255);
SELECT
@ProfileName = smp.name
, @AccountName = sma.name
, @AccountFromAddress = sma.email_address
, @AccountReplyTo = sma.replyto_address
, @SMTPServer = sms.servername
, @SMTPPort = sms.port
FROM msdb.dbo.sysmail_account sma
INNER JOIN msdb.dbo.sysmail_profileaccount smpa
ON sma.account_id = smpa.account_id
INNER JOIN msdb.dbo.sysmail_profile smp
ON smpa.profile_id = smp.profile_id
INNER JOIN msdb.dbo.sysmail_server sms
ON sma.account_id = sms.account_id;
/*
Confirm SQL Server Agent is configured to use Database Mail correctly
*/
DECLARE @res TABLE
(
[Value] varchar(255)
, [Data] varchar(255)
);
INSERT INTO @res
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail';
INSERT INTO @res
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile';
IF (
SELECT COUNT_BIG(*)
FROM @res r
WHERE r.[Value] = 'UseDatabaseMail' AND r.[Data] = 1
) = 1 AND
(
SELECT COUNT_BIG(*)
FROM @res r
WHERE r.[Value] = 'DatabaseMailProfile' AND r.[Data] IS NOT NULL
) = 1
SET @DatabaseMail = 'Configured'
ELSE
SET @DatabaseMail = 'Not Configured';
/*
Combine results into a single row
*/
SELECT
ServerName = @@SERVERNAME
, ProfileName = @ProfileName
, AccountName = @AccountName
, AccountFromAddress = @AccountFromAddress
, AccountReplyTo = @AccountReplyTo
, SMTPServer = @SMTPServer
, SMTPPort = @SMTPPort
, AgentDatabaseMail = @DatabaseMail;
The output looks like:
╔══════════════╦═════════════╦═════════════╦═══════════════════════╦════════════════════════╦════════════════════════╦══════════╦═══════════════════╗
║ ServerName ║ ProfileName ║ AccountName ║ AccountFromAddress ║ AccountReplyTo ║ SMTPServer ║ SMTPPort ║ AgentDatabaseMail ║
╠══════════════╬═════════════╬═════════════╬═══════════════════════╬════════════════════════╬════════════════════════╬══════════╬═══════════════════╣
║ servername ║ DBA ║ SMTP ║ [email protected] ║ [email protected] ║ mailserver.company.com ║ 25 ║ Configured ║
╚══════════════╩═════════════╩═════════════╩═══════════════════════╩════════════════════════╩════════════════════════╩══════════╩═══════════════════╝
Various parameters used by SQL Server Agent are stored in the registry. I use this code to get those details:
DECLARE @res TABLE
(
[Value] varchar(255)
, [Data] varchar(255)
);
INSERT INTO @res
EXEC master.dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE'
, @key = N'Software\Microsoft\MSSQLServer\SQLServerAgent'
, @value_name = N'DatabaseMailProfile';
INSERT INTO @res
EXEC sys.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE'
, @key = N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
, @value_name = N'UseDatabaseMail'
SELECT r.[Value]
, r.[Data]
FROM @res r;
The output:
╔═════════════════════╦═══════════════════╗
║ Value ║ Data ║
╠═════════════════════╬═══════════════════╣
║ DatabaseMailProfile ║ MV2012MailProfile ║
║ UseDatabaseMail ║ 1 ║
╚═════════════════════╩═══════════════════╝