Database mail

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,

Answer 1

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.

Answer 2

Hi, you could build a SSIS package and send an email at the end,  attaching SSIS user variables with the desired data.

Answer 3

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 .

Answer 4

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.

---