locked
Database mail RRS feed

  • Question

  • Hi All,

    I have a requirement that is supposed to send out 1 email to a Project Manger and list outstanding or completed tasks concerning that project, the query or Stored procedure uses columns like ProjectNames, Projectmanger, Task%Complete, col4, col5 and returns distinct ProjectNames when implicitly indicated in the query. However these return as expected multiple rows and for each row an email is generated.

    P.S I have tried using DISTINCT or Group by. Is there a way to send out a mail per project but containing multiple tasks.

    Thanks and regards,

    Wednesday, August 31, 2016 1:16 PM

Answers

All replies

  • first sql profile and use to send email

    use following 

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/03/28/send-table-or-view-as-embedded-html-lt-table-gt-in-an-email-stored-procedure.aspx

    http://surajpassion.in/sending-email-using-stored-procedures-in-sql-server/


    Please click Mark As Answer if my post helped.

    Wednesday, August 31, 2016 1:38 PM
  • Hi, you could build a SSIS package and send an email at the end,  attaching SSIS user variables with the desired data.
    Wednesday, August 31, 2016 2:04 PM
  • Hi ,

    As Enric suggested, you can use the Send Mail Task component from the SSIS, or you can use the SSRS to create a subscription, sent in by mail and even use parameters . On top of it, you can scheduale the subscription easily .

    Step 1 - Define the email box (or it is defined in the ssrs configuration manager) - http://bhushan.extreme-advice.com/email-configuration-in-ssrs-with-smtp-in-windows-server-2008r2/ .

    Step 2 - create a simple subscription - http://bhushan.extreme-advice.com/email-subscriptions-for-ssrs-reports/ .

    Good luck :)


    Regards, David .

    Wednesday, August 31, 2016 2:50 PM
  • Hi Durbslaw,

    As an alternative to all suggested methods, you can use the multi-row output for a SQL cursor Select statement

    And within the loop, you can use SQL Server database mail to send the emails one by one

    But it is best to use a SQL Server Reporting Services data-driven subscription method for such tasks.



    Wednesday, August 31, 2016 3:06 PM