Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

send email of all defaulters Expand / Collapse
Author
Message
Posted Tuesday, December 04, 2012 9:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 172, Visits: 450
Hi,
Please help on below scenario to send mail.

I am having a table which contains student attendance. If student is absent then the mail should automatically goes to HOD. But issue is that i don't want to send mail one for each student.
Suppose there is maths department and 7 students are absent then all 7 students name i want to send to HOD by the mail. I am able to send but 7 mails are going. Can you please help? its urgent.


Thanks
Abhas
Post #1392782
Posted Tuesday, December 04, 2012 10:43 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:45 AM
Points: 251, Visits: 538
you can use the procedure to first have the list of students
absent in a particular department and then use the procedure
sp_send_dbmail to send the list of absentees in the mail.
Post #1392788
Posted Tuesday, December 04, 2012 10:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 172, Visits: 450
Hi Jeet,

I want to use this by using SSIS.

Thanks
Abaso jadhav
Post #1392795
Posted Tuesday, December 04, 2012 11:29 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:30 AM
Points: 78, Visits: 195
abhas (12/4/2012)
Hi Jeet,

I want to use this by using SSIS.

Thanks
Abaso jadhav


Store the names of student in string variable using Execute Sql Task and then use send mail task to send mail.
Post #1392799
Posted Wednesday, December 05, 2012 12:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 172, Visits: 450
Hi rhythm

I am doing the same but emails are going repeatedly.
Example if there are 5 students then 5 different mails are going.

Thanks
Abhas.
Post #1392814
Posted Wednesday, December 05, 2012 1:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:30 AM
Points: 78, Visits: 195
abhas (12/5/2012)
Hi rhythm

I am doing the same but emails are going repeatedly.
Example if there are 5 students then 5 different mails are going.

Thanks
Abhas.


Probably you have include your mail task in loop container or so.
Please check and if not then attach screenshots or explain your package ?
Post #1392834
Posted Wednesday, December 05, 2012 10:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 172, Visits: 450
Hi rhythm,

I am doing below steps.

1) Execute sql task - here I am fetching studid,studname,deptname,HodName,HodMail.
2) I am using foreach loop
3) In foreach loop I am using script task and sendmail task

In script task I am fetching mailid's and writting body of message. But what happens in this case suppose there are 3 students belongs to physics dept then 3 separate mails are going to HOD.

I want to send single mail for three students as;

Dear Sir,
student1,student2,student3 are absent today.Please take required action.


Thanks
Abaso Jadhav
Post #1393320
Posted Thursday, December 06, 2012 4:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:30 AM
Points: 78, Visits: 195
abhas (12/5/2012)
Hi rhythm,

I am doing below steps.

1) Execute sql task - here I am fetching studid,studname,deptname,HodName,HodMail.
2) I am using foreach loop
3) In foreach loop I am using script task and sendmail task

In script task I am fetching mailid's and writting body of message. But what happens in this case suppose there are 3 students belongs to physics dept then 3 separate mails are going to HOD.

I want to send single mail for three students as;

Dear Sir,
student1,student2,student3 are absent today.Please take required action.


Thanks
Abaso Jadhav


Thats what I doubted.Use send mail task outside for each loop.
I hope you are using package level variable for stroing email ids.
Post #1393433
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse