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 ?
You can specify the
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 :
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