How to use sp_send_dbmail to multiple users?
-
When we would like to send an alert to more than one users, only the first one received the alert.
If we would like to send an alert to a group, it would be even worse, in particular, when the name of the group has free space in between.
Anyone came across this issue?
Any input will be greatly appreciated in advance.
-
the parameter @recipients for msdb.dbo.sp_send_dbmail expects a semicolon delimited list of recipients; there's no limit to the # of recipients, since it is a varchar(max) definition;
so something like this works just fine:
declare @body1 varchar(4000)
set @body1 = 'Sample Email from SQL2008 ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +
' '
EXEC msdb.dbo.sp_send_dbmail
@profile_name='gMail',
@recipients='lowell@yourdomain.com; "SQL Oracle" <sqloracle@anotherdomain.com>; thirdemail@domain.com',
@subject = 'SQl 2008 email test',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT top 3 * from sysobjects where xtype=''U''',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'qry.txt',
@query_result_no_padding = 1
now as for a group with a space in it, there is a difference betweent eh group's name and it's actual @domain adress; in Outlook you can simply go to the properties of the group and see what the actual GroupName @domainname.com really is. there is no valid email with a space in it, per whatever the email rules are, but the name attached to it can have spaces:
"My SQL Notification Group" <mysqlnotificationgroup@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! -
Lowell has explained it quite nicely. A recommendation would be to simply specify the email address of the group rather than the display name of the group.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQL ORACLE (8/28/2010)
When we would like to send an alert to more than one users, only the first one received the alert.if only one of the emails got thru, it might be that you are not using authenticated email, and only a user hosted by the mail server was allowed through;
to avoid spam, most email servers will not relay email to domain names the mail server does not directly service
Unless one of two things: the IP address sending the email is in a trusted list, or if the username and password was sent to authenticate.
double check your profile settings and see if these two fields are blank(anonymous authentication) or if you put in a valid username/password combo:
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!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply