February 25, 2010 at 7:00 am
I am trying to send an email which will contain at least one row (and in most cases 3-10 rows) of data. The rows are from #tmp_tableC. The possible universe of email addresses is around 200 unique email addresses.
I want to send 1 email per email address with multiple rows in the email, not 1 email per row of data. The code below works to gather the data and send the email. However at this point, it is sending 1 email per unique address, but including ALL records, not just the related records per email address. I have tried variations (JOIN, WHERE) to constrain the rows of data but have backed them out of the code to make it a little cleaner than it was.
I imagine a WHILE loop or a CURSOR would be the way to go, but I have limited experience with those. Would prefer to avoid CURSOR or WHILE loops if possible. Can anyone offer some guidance to help with this?
NOTE: The email process will be run once weekly at the end of the business day. The server which will be running this is not overused and can take some performance hits without affecting other users. I don't anticipate this scaling beyond more than around 300 unique emails in the next 3-5 years.
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @email NVARCHAR(MAX)
CREATE TABLE #tmp_tableC
(
fullname nvarchar(30),emailaddress1 nvarchar(max),acctname nvarchar(50), oppname nvarchar(150), rrname nvarchar(30), cusip nvarchar(9), newrate nvarchar(50), effdate datetime
)
Insert Into #tmp_tableC
[Some Select Query to gather data for the columns above]
CREATE TABLE #tmp_tableZ
(
cusipkey nvarchar(max)
)
Insert Into #tmp_tableZ
[Some Select Query that is a unique listing of email addresses]
--process emails
SET @email = ''
SELECT @email = @email + cusipkey + N';' FROM #tmp_tableZ group by cusipkey
SET @xml = Cast((SELECT oppname AS 'td','',cusip as 'td','', rrname as 'td','',newrate as 'td','',effdate as 'td'
FROM #tmp_tableC GROUP BY acctname,oppname,cusip,rrname,newrate,effdate FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))
SET @body ='<html><H2>Rate Reset Update</H2><body><table style="font-family: arial; font-size:10pt; border = 0"><tr bgcolor=gray><th>Opportunity Name</th><th>CUSIP</th><th>Issue</th><th>New Rate</th><th>Eff Date</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
print @email
EXEC msdb.dbo.sp_send_dbmail
@recipients = @email,
@body = @body,
@body_format ='HTML',
@subject ='Notification',
@profile_name ='Email Server'
Drop Table #tmp_tableC
Drop Table #tmp_tableZ
February 25, 2010 at 7:59 am
It's obvious you must execute sp_send_dbmail once for every receipient, right ?
So you're bound to a while loop or cursor anyway.
I hope this one puts you back on track ....
Here's a cursor version:
declare csrEmails cursor for
SELECT cusipkey, '<html><H2>Rate Reset Update</H2><body><table style="font-family: arial; font-size:10pt; border = 0"><tr bgcolor="#AAAAAA"><th>Opportunity Name</th><th>CUSIP</th><th>Issue</th><th>New Rate</th><th>Eff Date</th></tr>'
+ Cast((
SELECT oppname AS 'td'
, ''
, C.cusip as 'td'
, ''
, C.rrname as 'td'
, ''
, C.newrate as 'td'
, ''
, C.effdate as 'td'
FROM #tmp_tableC C
inner join #tmp_tableZ Z
on Z.cusipkey = C.cusipkey
GROUP BY acctname
, oppname
, cusip
, rrname
, newrate
, effdate
FOR
XML PATH('tr')
, ELEMENTS
) AS NVARCHAR(MAX)) + '</table></body></html>'
open csrEmails
FETCH NEXT FROM csrEmails
INTO @email, @body
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = @email
, @body = @body
, @body_format = 'HTML'
, @subject = 'Notification'
, @profile_name = 'Email Server'
-- read next
FETCH NEXT FROM csrEmails
INTO @email, @body
END
-- Cleanup cursors !!
CLOSE csrEmails
DEALLOCATE csrEmails
Drop Table #tmp_tableC
Drop Table #tmp_tableZ
Btw: in my test it returns "bgcolor=gray" is unknown, so I altered the to bgcolor="#AAAAAA"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 25, 2010 at 8:54 am
Thanks for the quick reply. It seems to be still including all records. To help illustrate the situation, here are the tables that 1) the queries create, and 2) the email that was generated from your version with Cursor.
My expected result is that the Hotmail account should get 4 rows and the Gmail account should get 3 rows.
TempTableC
myemail@gmail.com,test issue 3,test opp,12345TEST,0.66,2009-08-31
myemail@gmail.com,test issue 2,test opp,67890TEST,0.162009-08-31
myemail@gmail.com,test issue 1,test opp,54321TEST,0.44,2009-08-31
myemail@hotmail.com,test issue 4,Test Health Center,T35T4U789,0.07,2010-02-24
myemail@hotmail.com,test issue 3,test opp,12345TEST,0.66,2009-08-31
myemail@hotmail.com,test issue 2,test opp,67890TEST,0.16,2009-08-31
myemail@hotmail.com,test issue 1,test opp,54321TEST,0.44,2009-08-31
TempTableZ
Email table (same results at both addresses)
Opportunity,Name,CUSIP,Issue,NewRate,Eff Date
test opp,12345TEST,test issue 3,0.66,2009-08-31
test opp,54321TEST,test issue 1,0.44,2009-08-31
test opp,67890TEST,test issue 2,0.16,2009-08-31
Test Health Center,T35T4U789,test issue 4,0.070,2010-02-24,
February 26, 2010 at 12:22 am
You should have come to something like this ...
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @email NVARCHAR(MAX)
CREATE TABLE #tmp_tableC
( fullname nvarchar(30)
, emailaddress1 nvarchar(max)
, acctname nvarchar(50)
, oppname nvarchar(150)
, rrname nvarchar(30)
, cusip nvarchar(9)
, newrate nvarchar(50)
, effdate datetime
) ;
set nocount on
Insert Into #tmp_tableC values ('fullname','myemail@gmail.com','test issue 3','test opp','12345TEST',1,'0.66','2009-08-31')
Insert Into #tmp_tableC values ('fullname','myemail@gmail.com','test issue 2','test opp','67890TEST',1,'0.16','2009-08-31' )
Insert Into #tmp_tableC values ('fullname','myemail@gmail.com','test issue 1','test opp','54321TEST',1,'0.44','2009-08-31')
Insert Into #tmp_tableC values ('fullname','myemail@hotmail.com','test issue 4','Test Health Center','T35T4U789',2,'0.07','2010-02-24')
Insert Into #tmp_tableC values ('fullname','myemail@hotmail.com','test issue 3','test opp','12345TEST',2,'0.66','2009-08-31')
Insert Into #tmp_tableC values ('fullname','myemail@hotmail.com','test issue 2','test opp','67890TEST',2,'0.16','2009-08-31')
Insert Into #tmp_tableC values ('fullname','myemail@hotmail.com','test issue 1','test opp','54321TEST',2,'0.44','2009-08-31')
--[Some Select Query to gather data for the columns above]
CREATE TABLE #tmp_tableZ (cusipkey int , cusipemail nvarchar(max) ) ;
Insert Into #tmp_tableZ values (1,'myemail@gmail.com')
Insert Into #tmp_tableZ values (2,'myemail@hotmail.com')
declare csrEmails cursor for
SELECT Z.cusipemail, '<html><H2>Rate Reset Update</H2><body><table style="font-family: arial; font-size:10pt; border = 0"><tr bgcolor="#AAAAAA"><th>Opportunity Name</th><th>CUSIP</th><th>Issue</th><th>New Rate</th><th>Eff Date</th></tr>'
+ Cast((
SELECT C.oppname AS 'td'
, ''
, C.cusip as 'td'
, ''
, C.rrname as 'td'
, ''
, C.newrate as 'td'
, ''
, C.effdate as 'td'
FROM #tmp_tableC C
where C.cusip = Z.cusipkey
GROUP BY C.acctname
, C.oppname
, C.cusip
, C.rrname
, C.newrate
, C.effdate
FOR
XML PATH('tr')
, ELEMENTS
) AS NVARCHAR(MAX)) + '</table></body></html>' as Body
FROM #tmp_tableZ Z
open csrEmails
FETCH NEXT FROM csrEmails
INTO @email, @body
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = @email
, @body = @body
, @body_format = 'HTML'
, @subject = 'Notification'
, @profile_name = 'Email Server'
-- read next
FETCH NEXT FROM csrEmails
INTO @email, @body
END
-- Cleanup cursors !!
CLOSE csrEmails
DEALLOCATE csrEmails
Drop Table #tmp_tableC
Drop Table #tmp_tableZ
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 26, 2010 at 8:39 am
After wiring up the select queries, it worked perfectly. Thanks for the help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply