Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dtrivedi
Posting Yak Master
153 Posts |
 Posted - 2010-07-20 : 11:53:56
|
| This query run fine. it sends me the emails that i need.here is the query:use ANALYTICS create table #ezpayloc (locations varchar(50), storeemail varchar(50))insert into #ezpayloc SELECT distinct [LOCATION], +('mac'+(location)+'@maclocal.com') FROM vw_PAST_DUE_LESSONS_EZPAY] order by location select * from #ezpayloc SELECT distinct [LOCATION], +('mac'+(location)+'@maclocal.com') FROM vw_PAST_DUE_LESSONS_EZPAY] order by locationGODECLARE @locs varchar(max)declare @email varchar(max)declare t_ezpay cursor for select storeemail from #ezpaylocdeclare t_ezpay cursor for select locations from #ezpaylocopen t_ezpayfetch next from t_ezpay into @emailfetch next from t_ezpay into @locs while @@FETCH_STATUS = 0 BEGIN Print 'Processing: ' + @email EXEC msdb.dbo.sp_send_dbmail@recipients = 'dtrivedi@musicarts.com',@body= 'All attempts to charge the credit card on file for the following EZ Pay Customers has failed. Please contact the customer, update the card on file and charge the balance to the new card.',@query='SELECT [LOCATION],[CUSTOMER],[ACCOUNT],[CUSTOMER_NAME],[PHONE] FROM vw_PAST_DUE_LESSONS_EZPAY]' , @subject = 'EZ Pay' fetch next from t_ezpay into @email ENDclose t_ezpaydeallocate t_ezpaydrop table #ezpaylocnow what i need it to do is with each time the query fetches i need it to take the results and email it to the location specific to it for ex if it runs location cutomer 0011 12340011 12510011 42500012 15060012 1508so when it send an email to location 0011, location 0011 should only get their info as it the first 3 resultsdoes that make sense? |
|
|
|
|
|
WHERE CLAUSE