We help IT Professionals succeed at work.

Issue sending to multiple addresses using sp_send_dbmail SQL 2012

mburk1968
mburk1968 asked
on
32 Views
Last Modified: 2016-04-20
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;
Comment
Watch Question

Author

Commented:
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 CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
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.

Author

Commented:
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.
Database Engineer
CERTIFIED EXPERT
Top Expert 2005
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
That is a good question. Would it be permissions for the database server to relay emails? Or the creator? In this case me?
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:

Author

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.