Is there anyway to use a People Distribution List with DBMail?

Question

Is there anyway to use a People Distribution List with DBMail?

sql-lover

SSCoach

Points: 18530
More actions
March 13, 2015 at 7:42 am

#398177

I would like to replace mymail@mail.com by mypdl@mail.com , where mypdl is basically a people distribution list with 2 emails:my own email and my coworker and fellow DBA.
IF EXISTS
(
SELECT TOP 1 [PercentUsed]
FROM [BackupSize]
WHERE PercentUsed>=70 AND [drivename]='D:\' -- D is the internal drive
ORDER BY [MetricDate] DESC
)
BEGIN
USE msdb
EXEC sp_send_dbmail
@profile_name = 'DBMAIL',
@recipients = 'mymail@mail.com',
@subject = 'Backup server''s current space',
@body = 'The backup server is running out of space',
@execute_query_database = 'msdb'
END
But I think this won't work by design. Maybe I need to create an operator?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i beleive groups are a function of the mails erver, which generates individual copies of the email to people assinged tot eh group that the smtp alias points to;

i know exchange does gorups, but it's a function of the mails erver itself.

@recipients = 'mygroup@somedowmain.com' in my org might work, but i'd have to get with our exchange guy to map an SMTP address to a distribution list.

easiest fix is just to put all the emails in there now i guess

@recipients = 'mymail@somedomain.com;mycoworkersmail@somedomain.com;theboss@somedomain.com',

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ABAS101 SSCommitted Points: 1894 More actions · Answer 2

I created an operator with multiple names and then just send it using the below? There may be some limitations (maybe) but I'm not sure what all you'll be using it for down the road. (added down the road 🙂 )

EXEC msdb..sp_notify_operator

@profile_name = N'dbmail', -- email profile

@name = 'My Operator Name Here', -- operator name

@subject = N'Sending this to a single operator',

@body = N'All emails in the operator list should get the email';

GO

sql-lover SSCoach Points: 18530 More actions · Answer 3

Lowell (3/13/2015)
i beleive groups are a function of the mails erver, which generates individual copies of the email to people assinged tot eh group that the smtp alias points to;
i know exchange does gorups, but it's a function of the mails erver itself.
@recipients = 'mygroup@somedowmain.com' in my org might work, but i'd have to get with our exchange guy to map an SMTP address to a distribution list.
easiest fix is just to put all the emails in there now i guess
@recipients = 'mymail@somedomain.com;mycoworkersmail@somedomain.com;theboss@somedomain.com',

True, and you're right, it is by design, but that's not what I want.

I think I found a workaround years ago but don't remember what I did, maybe an operator and the pdl there?

Lowell SSC Guru Points: 323518 More actions · Answer 4

as far as i know sp_send_DbMail requires email addresses int he correct format; it will not work with an operator name.

all my SQLAgent my operators point to sqlAlerts@mydomain.com, which is an SMTP alias which in turn points to an exchange distribution list on the exchange server.

but i cannot use the operator "DBA Team" as the @Recipient, i have to use the sqlalerts@mydomain.com in sp_send_dbmail.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!