How to select the mail profile in a sql agent job 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 ?
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.