asked
stored procedure then sending email based on results
SELECT PA.dtmCreated AS [Date Submitted] , PA.controlnum AS [PA Number] , PA.dlrnum AS [Customer Number] , PA.totalclaim AS [Estimated $ Claimed] , PA.reason AS Description , MAM.txtCommentClient , MAM.txtFirstName , MAM.txtLastName , MAM.txtEMail FROM [SQL1mjh0].test1 AS MAM INNER JOIN [SQL1mjh0].test2 AS PA ON MAM.Region = PA.Region AND PA.Territory = MAM.Territory AND PA.District = MAM.Dictrict The above select statment creates lines of information that will vary every week. I need to run the stp then send email to each individual listed. Does anyone have any idea how i should do this. i've crated jobs that send out emails but this is definitely different than anything i have done.
Well, I'd probably do this using a WHILE loop in conjunction with sp_send_dbmail. You could put all of email addresses into a temp table and then iterate through it processing each row individually. sp_send_dbmail:
http://msdn.microsoft.com/en-us/library/ms190307.aspx WHILE:
http://msdn.microsoft.com/en-us/library/ms178642.aspx You can also do this with SSIS however probably more complicated.
http://www.mssqltips.com/sqlservertip/1731/sending-email-from-sql-server-integration-services-ssis/ Or can you put all of the email address into a distribution group and just email the distribution group or do the results change frequently? Just a thought. Hope this helps!
This will change weekly. I'm only running the job(when i finally get it created) on Fridays.
14 People are following this question.
