locked
SQL agent configured to call a SP RRS feed

  • Question

  • All,

    My problem scenarion is:

    "I have a SQL agent job setup that calls a SP which collects all data and sends a report to some email ids via msdb.dbo.sp_send_dbmail.

    The job is scheduled to run daily once around 8:30 PM. Below T-SQL is part of the SP to send the email

    exec  msdb.dbo.sp_send_dbmail @recipients=@EMAILID,@blind_copy_recipients=@BCC, 
        @subject = @Subject1 , 
        @body = @body1, 
        @body_format = 'HTML' ; 
    END"

    Users say that they do receive email on the report but they receive 2 similar emails at the same time.

    But the job is scheduled to run only once a day and there is no duplicate job to send email for the same report and to same users.

    How do I trouble shoot from where the second email is going?


    Regards, Sushant

    Sunday, June 9, 2013 8:48 AM

Answers

  • Hello Sushant,

    Check the mail log to see if they are really send twice:

    SELECT *
    FROM msdb.[dbo].[sysmail_allitems]


    Olaf Helper

    Blog Xing

    • Proposed as answer by Dean Savović Monday, June 10, 2013 7:59 AM
    • Marked as answer by T2SQLDBA Thursday, June 13, 2013 10:13 AM
    Sunday, June 9, 2013 9:47 AM
  • I would ask the users to send me a copy of "both" emails before proceeding to troubleshoot further.

    I would want to validate that they are in fact the same identical email and not a SQL Agent Job notification email or something.


    John Sansom| @SQLBrit

    • Marked as answer by T2SQLDBA Thursday, June 13, 2013 10:13 AM
    Sunday, June 9, 2013 9:49 AM

All replies

  • Hi 

    If you remove @blind_copy_recipients param, same result? I think you need to work closely with your Network Admin (Exchange Admin) to find out why it is happening.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, June 9, 2013 8:55 AM
    Answerer
  • Hi Uri,

    @blind_copy_recipients param is not a problem I have marked my email id in both @Recipient and @Blind_copy_recipents param and I received the email only once.


    Regards, Sushant

    • Marked as answer by T2SQLDBA Thursday, June 13, 2013 10:13 AM
    • Unmarked as answer by T2SQLDBA Thursday, June 13, 2013 10:13 AM
    Sunday, June 9, 2013 9:28 AM
  • Hello Sushant,

    Check the mail log to see if they are really send twice:

    SELECT *
    FROM msdb.[dbo].[sysmail_allitems]


    Olaf Helper

    Blog Xing

    • Proposed as answer by Dean Savović Monday, June 10, 2013 7:59 AM
    • Marked as answer by T2SQLDBA Thursday, June 13, 2013 10:13 AM
    Sunday, June 9, 2013 9:47 AM
  • I would ask the users to send me a copy of "both" emails before proceeding to troubleshoot further.

    I would want to validate that they are in fact the same identical email and not a SQL Agent Job notification email or something.


    John Sansom| @SQLBrit

    • Marked as answer by T2SQLDBA Thursday, June 13, 2013 10:13 AM
    Sunday, June 9, 2013 9:49 AM
  • Hi Uri/Olaf/John,
    Thanks for your replies.

    I have used below script

    SELECT *
    FROM msdb.[dbo].[sysmail_allitems]

    And also asked user to share with me the email notification they received.

    I could solve the problem.

    Regards, Sushant

    Thursday, June 13, 2013 10:14 AM
  • It would be great if you can share how did you solve the issue. 

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, June 13, 2013 10:18 AM