Avatar of mburk1968
mburk1968Flag for United States of America asked on

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_RoyaltyCache T1
        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-19532FAC7581';
    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;
Microsoft SQL Server

Avatar of undefined
Last Comment
mburk1968

8/22/2022 - Mon
ASKER
mburk1968

I should also mention that the script does not generate any errors however no mater what position I place my email address in I am the only one who receives it.
Brian Crowe

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
mburk1968

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_RoyaltyCache T1
        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-19532FAC7581';
    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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Brian Crowe

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
mburk1968

That is a good question. Would it be permissions for the database server to relay emails? Or the creator? In this case me?
Brian Crowe

ASKER
mburk1968

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.