Configuring Database Mail with an SQL Server Agent Operator with multiple email addreses.

• HookSQLDBA7

  SSChampion

  Points: 10709

  More actions

  July 29, 2013 at 2:26 pm

  #297014

  We are running SQL Server 2012 SP1 64-Bit EE on Windows Server 2008 R2 SP1. (To create an Operator Group, go to SQL Server Agent, Operators, DBA Group Properties and specify 'DBA Group' beside the 'Name:' Box and the email addresses in the 'E-mail Name:' Box.) Use the code below to associate the Operator with email addresse. The first piece of code updates the Operator with an Exchange Distribution Email Address (sqldba@school.net). The Exchange Distribution Email Address points to 3 email addresses. This works fine.

  USE [msdb]

  GO

  EXEC msdb.dbo.sp_update_operator @name=N'DBAGroup1',

  @enabled=1,

  @pager_days=0,

  @email_address=N'sqldba@school.net',

  @pager_address=N'',

  @netsend_address=N''

  GO

  The second piece of code updates the Operator with 3 individual email addresses. With the Operator set up this way, it does not work; the email is not sent. The Error Message is listed below (I have tried the email addresses with a comma in between.)

  USE [msdb]

  GO

  EXEC msdb.dbo.sp_update_operator @name=N'DBAGroup2',

  @enabled=1,

  @pager_days=0,

  @email_address=N'TestEmail1@school.net;TestEmail2@school.net;TestEmail3@school.net',

  @pager_address=N'',

  @netsend_address=N''

  GO

  --NOTE:

  --The below code does NOT WORK when sending to a SQL Server Agent Operator's

  --Group (i.e. DBAGroup2) with more than one email address.

  --Send_Mail_via_DBMail.sql

  EXEC msdb.dbo.sp_send_dbmail

  @recipients='DBAGroup2', --(Does Not Work)

  @body= 'Test Email Body',

  @subject = 'Test Database Email',

  @profile_name = 'TestDB Administrator Profile',

  @file_attachments = 'D:\DBA Scripts\AlterDatabases\Alter_Database_Options.sql'

  This is the Error Message, located in the Database Mail Log is:

  "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2013-07-29T16:09:15). Exception Message: Cannot send mails to mail server. (A recipient must be specified.)."

  I thought I had this working via the GUI (SQL Server Agent, double click a Job, click the Notification Page) with an older version of SQL Server (2005 I believe). I had Jobs set up that would send emails to an Operator Group which contained multiple individual Microsoft Exchange Email Addresses verses an Exchange Distribution Group. And thoughts? With SQL Server 2012 must we now use an Exchange Distribution Email?

  Thanks in advance, Kevin