SQL - Configure Database Mail

Question

I'm having a issue with my database mail not showing my new profile I created. I'm executing this:

    Execute msdb.dbo.sysmail_add_account_sp   
@account_name =   'email here',  
@email_address =  'email here' ,  
    @display_name =  'White Box Gaming' ,    
    @replyto_address =  'email here' ,    
    @description =  'Profile used to send mail' ,    
@mailserver_name =  'smtp.gmail.com',   
@mailserver_type =  'SMTP',   
@port =  587,    
@username =  'email here',    
@password =  'password',       
@enable_ssl = 0

No errors but when I try to view it:

select *
from msdb.dbo.sysmail_profile p 
join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id 
join msdb.dbo.sysmail_account a on pa.account_id = a.account_id 
join msdb.dbo.sysmail_server s on a.account_id = s.account_id

New profile does not appear. What did I miss?

score 1 · Accepted Answer · answered Apr 29 '20 at 13:18

After creating the mail account, assign it to a mail profile (the query joins sysmail_profile with sysmail_profileaccount)

--get profiles
EXEC msdb.dbo.sysmail_help_profile_sp;

--if there is no profile, create one
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = N'my email profile', @description = N'email profile description';


--get accounts (note down the account_id)
EXEC msdb.dbo.sysmail_help_account_sp;

--get profiles (note down the profile_id)
EXEC msdb.dbo.sysmail_help_profile_sp;

--add account to profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_id= 1/*profile_id goes here*/,  @account_id = 1 /*account_id goes here*/, @sequence_number=1;

--list accounts for each profile
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;
--or
select *
from msdb.dbo.sysmail_profile p 
join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id 
join msdb.dbo.sysmail_account a on pa.account_id = a.account_id 
join msdb.dbo.sysmail_server s on a.account_id = s.account_id;

SQL - Configure Database Mail

1 Answers1