Database Mail - Configuring

To set up SQL Server Agent Mail to use Database Mail

·         In Object Explorer, expand a server.

·         Right-click SQL Server Agent, and then click Properties.

·    Click Alert System.

·         Select Enable Mail Profile.

·         In the Mail system list, choose Database Mail.

·         In the Mail profile list, select a mail profile for Database Mail.

--Not entirely sure if this works

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';

--Shows principle id, name and profile (not sure if it’s helpful, but included anyways)

EXEC msdb.dbo.sysmail_help_principalprofile_sp;

--To start Database Mail; although it does start itself when needed

EXEC msdb.dbo.sysmail_start_sp;

--See Sent email

SELECT * FROM msdb.dbo.sysmail_sentitems;

--Email event log to see if info, warnings, and errors

     SELECT * FROM msdb.dbo.sysmail_event_log;

--To get number of unsent emails

    select count(*) from msdb.dbo.sysmail_unsentitems;

-- remove all the unsent emails

   --delete from msdb.dbo.sysmail_unsentitems;

http://msdn2.microsoft.com/en-us/library/ms186358(d=ide).aspx.

https://www.sqlservercentral.com/Forums/Topic263951-92-1.aspx