How to select the mail profile in a sql agent job

Question

I have several mail profiles configured on sql agent, how can I choose which of these profiles gets used for sending out notifications on a specific sql agent job ?

Answer 1

Database Mail profiles work a bit differently when it comes to SQL Agent jobs. In configuring SQL Agent itself through the properties window you will configure the Alert System.

The two arrows point to where a database mail profile can be selected. In these two instances the profile you select is basically telling SQL Agent this is your SMTP configuration and who you are going to send the emails "as", or the "FROM" field. Configuring the Alert System does require restarting the SQL Agent service.

Once that is done in order to receive notifications for SQL Agent jobs you will need to configure Operators.

You would setup the operator to note who you want the notification to be sent "TO". You can put more than one email address in this field but it does have a character limit. You can create multiple operators but within the notifications page of the job properties you will notice it only allows you to select one. This is just the limitation of SQL Agent.

The alternative to this would be using the sp_send_db_mail within the job step itself. In this you can use any mail profile you want, in the event you wanted it to go to someone other than any operator you may have configured.

Answer 2

You can specify the profile_name in sp_send_dbmail as below :

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'here goes your profile  name'

Refer to BOL for more details.

You can Lists the accounts associated with one or more Database Mail profiles using sysmail_help_profileaccount_sp

Below is the t-sql that will give you all the details :

SELECT  [sa].[account_id]
, [sa].[name] as [Profile_Name]
, [sa].[description]
, [sa].[email_address]
, [sa].[display_name]
, [sa].[replyto_address]
, [ss].[servertype]
, [ss].[servername]
, [ss].[port]
, [ss].[username]
, [ss].[use_default_credentials]
, [ss].[enable_ssl]
FROM 
 msdb.dbo.sysmail_account sa
INNER JOIN msdb.dbo.sysmail_server ss
ON  sa.account_id = ss.account_id