Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
Are you asking how to actually do it, or which one to actually pick and why? –  Shawn Melton Jan 6 at 16:22
    
I'm asking how to actually do it in a SQL Agent job, I can't find a way to specify per job which mail profile should be used. Since in these SQL Agent jobs you only have the Notifications dialog where you can specify the recipient and not the sender (which would be the profile's purpose I guess). –  nojetlag Jan 7 at 7:15

2 Answers 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
share|improve this answer

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. enter image description here

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.

enter image description here

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.