Issue sending to multiple addresses using sp_send_dbmail SQL 2012
I have the following script which for some reason is only sending the email to me. Me being "me@email.com" Why am I unable to send to others in my domain?
DECLARE @Style CHAR(12) ,
@BodyMessage NVARCHAR(MAX);
SELECT @BodyMessage = N'There were no Royalty changes posted';
SELECT @Style = T2.style
FROM KLL_Cust.dbo.Report_Royalt
FULL JOIN dbo.zzxstylr T2 ON T1.division = T2.division
AND T1.style = T2.style
WHERE T1.roy_cls <> T2.roy_cls
OR T1.royalty <> T2.royalty;
IF @Style > = 0
BEGIN
EXEC msdb.dbo.sp_start_job @job_name = 'D612C861-179A-4090-94F3-1
END;
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients = 'person1@email.com; me@email.com; otherguy@email.com',
@subject = 'Royalty Change Report', @body = @BodyMessage;
END;
DECLARE @Style CHAR(12) ,
@BodyMessage NVARCHAR(MAX);
SELECT @BodyMessage = N'There were no Royalty changes posted';
SELECT @Style = T2.style
FROM KLL_Cust.dbo.Report_Royalt
FULL JOIN dbo.zzxstylr T2 ON T1.division = T2.division
AND T1.style = T2.style
WHERE T1.roy_cls <> T2.roy_cls
OR T1.royalty <> T2.royalty;
IF @Style > = 0
BEGIN
EXEC msdb.dbo.sp_start_job @job_name = 'D612C861-179A-4090-94F3-1
END;
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients = 'person1@email.com; me@email.com; otherguy@email.com',
@subject = 'Royalty Change Report', @body = @BodyMessage;
END;
It is either an issue with your profile definition or your permissions on the SMTP server that you are using. I notice you aren't selecting a profile for sp_send_dbmail.
[ @profile_name = ] 'profile_name'
Is the name of the profile to send the message from. The profile_name is of type sysname, with a default of NULL. The profile_name must be the name of an existing Database Mail profile. When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user. If the user does not have a default private profile, sp_send_dbmail uses the default public profile for the msdb database. If the user does not have a default private profile and there is no default public profile for the database, @profile_name must be specified.
ASKER
This is what I have now.
principal_id principal_name profile_id profile_name is_default
2 guest 1 KLNYBCDB01 Mail 0
2 guest 2 DB Mail 1
DECLARE @Style CHAR(12) ,
@BodyMessage NVARCHAR(MAX);
SELECT @BodyMessage = N'There were no Royalty changes posted';
SELECT @Style = T2.style
FROM KLL_Cust.dbo.Report_Royalt
FULL JOIN dbo.zzxstylr T2 ON T1.division = T2.division
AND T1.style = T2.style
WHERE T1.roy_cls <> T2.roy_cls
OR T1.royalty <> T2.royalty;
IF @Style > = 0
BEGIN
EXEC msdb.dbo.sp_start_job @job_name = 'D612C861-179A-4090-94F3-1
END;
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients = 'person1@email.com',
@copy_recipients = 'me@email.com',
@profile_name = 'DB Mail', @subject = 'Royalty Change Report',
@body = @BodyMessage;
END;
I'm still the only one who receives the mail.
principal_id principal_name profile_id profile_name is_default
2 guest 1 KLNYBCDB01 Mail 0
2 guest 2 DB Mail 1
DECLARE @Style CHAR(12) ,
@BodyMessage NVARCHAR(MAX);
SELECT @BodyMessage = N'There were no Royalty changes posted';
SELECT @Style = T2.style
FROM KLL_Cust.dbo.Report_Royalt
FULL JOIN dbo.zzxstylr T2 ON T1.division = T2.division
AND T1.style = T2.style
WHERE T1.roy_cls <> T2.roy_cls
OR T1.royalty <> T2.royalty;
IF @Style > = 0
BEGIN
EXEC msdb.dbo.sp_start_job @job_name = 'D612C861-179A-4090-94F3-1
END;
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients = 'person1@email.com',
@copy_recipients = 'me@email.com',
@profile_name = 'DB Mail', @subject = 'Royalty Change Report',
@body = @BodyMessage;
END;
I'm still the only one who receives the mail.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is a good question. Would it be permissions for the database server to relay emails? Or the creator? In this case me?
Whatever credentials you provided in your mail profile.
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/
ASKER
Thank you. It was the permissions for the outgoing SMTP server. You really pointed me in the right direction with all of your post however this was the icing on the cake.
Now I can send data driven reports using SQL Standard.
Thank You
Now I can send data driven reports using SQL Standard.
Thank You
ASKER