Send mail to a more than one email id in Sql server agent

Question

Send mail to a more than one email id in Sql server agent

Analyst

Ten Centuries

Points: 1063
More actions
May 15, 2008 at 9:31 am

#186641

Hi,
Have a created job which contains n number of steps.
Have also created a database mail and an operator. In the operator am able to set only one email id for the 'To' Address.
But,I would like to send a mail notification to more than one email id.
Could someone please tell how to send the email to more than one id?
Thanks in advance!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

thasteve SSCrazy Points: 2707 More actions · Answer 1

Hello you can use a comma separated list as email in an operator but it still limited to a few characters so best practice would be to use an distributed email address by your mail server.

Analyst Ten Centuries Points: 1063 More actions · Answer 2

Thanks for the reply.

I would also like clarification for the below:

1.What is the max character size for the email in the operator( 'To'

address)?

2.In the sql server agent, under notification ->Is there a way to choose more than one operator (in the dropdown) to send email?

Awaiting for reply!

the__t Hall of Fame Points: 3260 More actions · Answer 3

1. @email_address = nvarchar(100) (sp_add_operator)

2. No, you can only choose one.

Using database mail, though, you can get around this, I believe, by executing stored procedure msdb.dbo.sp_send_dbmail. The @recipient parm is varchar (max).

Example:

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'testprofile',@recipients = 'person1@email.com; person2@email.com; person3@email.com',@body = 'test',@subject = 'test'

"Got no time for the jibba jabba!"
-B.A. Baracus

thasteve SSCrazy Points: 2707 More actions · Answer 4

I got an operator with 3 email addresses separated by a ; so this is of course working with the dbmail not smtp. In agent jobs where i want some kind of report other than job was successful or not I got a job step to build the report and send it to the operator with the listing of emails with directly selecting them by using SELECT email_address FROM msdb.dbo.sysoperators where id = operator you could also use it building your own string of addresses seperated by a ;

Analyst Ten Centuries Points: 1063 More actions · Answer 5

Where is it that we can specify the database mail in the sql server agent (for sending multiple recipients)

I was thinking the database mail is setup for the purpose of sending emails(Default From Address) from the sql server.

Analyst Ten Centuries Points: 1063 More actions · Answer 6

Hi thatseve,

My requirement is only in the end of n number of steps (package), i ll be sending teh mail..

And even if one of the step in the middle fails, the job will fail , hence i cant create a new step and send the report to n number of addresses.

Bindu-330959 Old Hand Points: 362 More actions · Answer 7

Hi

You can add this send mail component in Event handlers, where you can specify onfailure of the task, perform the action. It could be sending an email alert, updating log etc.

Hope this helps

Regards

Bindu

Analyst Ten Centuries Points: 1063 More actions · Answer 8

Thanks all for the responses.

Currently,have added the email recipiemts in the operator using a ; and it seems to be working fine.