February 7, 2013 at 11:42 am
Hello Everyone
I hope that everyone is having a fabulous day.
I am working on setting up database mail on a SQL 2008R2 box. I would like to be able to send out an email on a schedule after selecting some values from a table filled with stats. I am looking for some guidance on creating an "email template" that I can use.
I am hoping to be able to use some html formatting to make the email look better than plain text. Is this even possible?
Does anyone have some nice code for selecting values from a table to be included in the body of the email? May I select from a list of email addresses being stored in a table?
Thank you all in advance for your assistance, suggestions and help
Andrew SQLDBA
February 7, 2013 at 12:11 pm
Andrew From experience, doing it all in TSQL can get very wordy and long. the headers require a lot of customization to match the shape / number of columns in any data you are including.
I've done this a lot, and it ends up requiring quite a bit of customization, but having a model to get started helps for sure.
I've done it via a CLR and in TSQL both.
if you are going to send customized emails to individuals (ie one email per address) you'll need a cursor or loop so you can call msdb.dbo.sp_send_dbmail
if the email gets customized, ie swapping out content that is unique to the email, then that is done in the loop also.
here's something i currently use that sends Daily Emails out in a nice HTML format.
It combines two tables...basic holiday notifications, and also another table that we can insert various events reminders or notification into.
i'm posting it so you can see how i do it; others may chime in on better ways, but this seems to work for me. if you need the actual table definitions for a copy/paste example, i'll post them as well, but you can kind of see that you need to fiddle with:
/*
--If there is not data, nothing will be sent; i know there is a double select here, but....
*/
IF EXISTS(SELECT *
FROM SandBox.dbo.TallyCalendar cal
LEFT OUTER JOIN SandBox.dbo.TallyEvents evt
ON cal.[TheDate] = evt.[TheDate]
WHERE (cal.TheDate
BETWEEN DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
AND DATEADD(dd,8,DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0))
AND cal.[isholiday] = 1)
OR (evt.[TheDate] BETWEEN DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
AND DATEADD(dd,8,DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0))
AND evt.[IsHolidayorReminder] = 0
)
UNION
SELECT *
FROM SandBox.dbo.TallyCalendar cal
LEFT OUTER JOIN SandBox.dbo.TallyEvents evt
ON cal.[TheDate] = evt.[TheDate]
WHERE cal.TheDate = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
AND (cal.TheDate = evt.[TheDate]
AND evt.[IsHolidayorReminder] = 1)
)
BEGIN
SET NOCOUNT ON;
DECLARE @MailBody VARCHAR(MAX),
@TableHead VARCHAR(MAX),
@TableTail VARCHAR(MAX)
SET @TableTail = '</table></body></html>';
SET @TableTail = '</table></body></html>';
SET @TableHead = '<html><head>'
+ '<style>'
+ 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} '
+ '</style>'
+ '</head>'
+ '<body><table cellpadding=0 cellspacing=0 border=0>'
+ '<tr bgcolor=#FFEFD8><td align="center">{CustomMessage}</td></tr>'
+ '<tr bgcolor=#FFEFD8>'
+ '<td align="center"><b>TheDate</b></td>'
+ '<td align="center"><b>DayOfWeek</b></td>'
+ '<td align="center"><b>IsHoliday</b></td>'
+ '<td align="center"><b>IsWorkHoliday</b></td>'
+ '<td align="center"><b>IsWeekDay</b></td>'
+ '<td align="center"><b>IsWeekEnd</b></td>'
+ '<td align="center"><b>IsDaylightSavings</b></td>'
+ '<td align="center"><b>HolidayName</b></td>'
+ '<td align="center"><b>LunarPhase</b></td>'
+ '<td align="center"><b>IsoWeek</b></td>'
+ '<td align="center"><b>JulianDay</b></td>'
+ '<td align="center"><b>YearNumber</b></td>'
+ '<td align="center"><b>MonthNumber</b></td>'
+ '<td align="center"><b>DayNumber</b></td>'
+ '</tr>'
SELECT
ROW_NUMBER() OVER(ORDER BY cal.[TheDate]) % 2 AS [TRRow],
cal.[TheDate] ,
cal.[DayOfWeek],
cal.[IsHoliday],
cal.[IsWorkHoliday],
cal.[IsWeekDay],
cal.[IsWeekEnd],
cal.[IsDaylightSavings],
ISNULL(cal.[HolidayName],'') + ISNULL(evt.[EventDescrip],'') AS [HolidayName],
cal.[LunarPhase],
cal.[IsoWeek],
cal.[JulianDay],
cal.[YearNumber],
cal.[MonthNumber],
cal.[DayNumber]
INTO #tmp
FROM SandBox.dbo.TallyCalendar cal
LEFT OUTER JOIN SandBox.dbo.TallyEvents evt
ON cal.[TheDate] = evt.[TheDate]
WHERE (cal.TheDate
BETWEEN DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
AND DATEADD(dd,8,DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0))
AND cal.[isholiday] = 1)
OR (evt.[TheDate] BETWEEN DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
AND DATEADD(dd,8,DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0))
AND evt.[IsHolidayorReminder] = 0
)
UNION
SELECT
ROW_NUMBER() OVER(ORDER BY cal.[TheDate]) % 2 AS [TRRow],
cal.[TheDate] ,
cal.[DayOfWeek],
cal.[IsHoliday],
cal.[IsWorkHoliday],
cal.[IsWeekDay],
cal.[IsWeekEnd],
cal.[IsDaylightSavings],
ISNULL(cal.[HolidayName],'') + ISNULL(evt.[EventDescrip],'') AS [HolidayName],
cal.[LunarPhase],
cal.[IsoWeek],
cal.[JulianDay],
cal.[YearNumber],
cal.[MonthNumber],
cal.[DayNumber]
FROM SandBox.dbo.TallyCalendar cal
LEFT OUTER JOIN SandBox.dbo.TallyEvents evt
ON cal.[TheDate] = evt.[TheDate]
WHERE cal.TheDate = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
AND (cal.TheDate = evt.[TheDate]
AND evt.[IsHolidayorReminder] = 1)
SELECT
@MailBody =(SELECT
ROW_NUMBER() OVER(ORDER BY cal.[TheDate]) % 2 AS [TRRow],
cal.[TheDate] AS
,
cal.[DayOfWeek] AS
,
cal.[IsHoliday] AS
,
cal.[IsWorkHoliday] AS
,
cal.[IsWeekDay] AS
,
cal.[IsWeekEnd] AS
,
cal.[IsDaylightSavings] AS
,
ISNULL(cal.[HolidayName],'') AS
,
cal.[LunarPhase] AS
,
cal.[IsoWeek] AS
,
cal.[JulianDay] AS
,
cal.[YearNumber] AS
,
cal.[MonthNumber] AS
,
cal.[DayNumber] AS
FROM #tmp cal
ORDER BY cal.[TheDate]
FOR XML RAW('tr'), Elements)
-- Replace the entity codes and row numbers
SET @MailBody = REPLACE(@MailBody, '_x0020_', SPACE(1))
SET @MailBody = REPLACE(@MailBody, '_x003D_', '=')
SET @MailBody = REPLACE(@MailBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @MailBody = REPLACE(@MailBody, '<TRRow>0</TRRow>', '')
DECLARE @CustomMessage varchar(max)
--might be somethign specific to this report, or just some standard boiulerplate stuff
SELECT TOP 1 @CustomMessage = RandomQuote FROM HistoricalQuotes ORDER BY NEWID();
--custom message? if not modified above, erase it.
SET @MailBody = REPLACE(@MailBody, '{CustomMessage}', '') --{CustomMessage}
SELECT @MailBody = @TableHead + @MailBody + @TableTail
-- return output
--SELECT @Body
-- --CREATE Profile [SomeDomain DBMail]
--NOW we can finally email something.
EXEC msdb.dbo.sp_send_dbmail
@profile_name='SomeDomain DBMail',
@recipients='lowell@SomeDomain.com',
@subject = 'Holiday And Event Notifications for the Next 7 Days',
@body = @MailBody,
@body_format = 'HTML'
DROP TABLE #tmp
END
Crappy untested example of a loop:
declare
@FirstName varchar(50),
@email varchar(100),
@Subject varchar(100)
declare c1 cursor for
select firstname,email from SandBox.dbo.AllEmails where DailyNotifications = 1
open c1
fetch next from c1 into @FirstName,@email
While @@fetch_status <> -1
begin
SET @Subject = @Firstname + ', here are the Holiday And Event Notifications for the Next 7 Days'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='SomeDomain DBMail',
@recipients= @email
@subject = @Subject,
@body = @MailBody,
@body_format = 'HTML'
fetch next from c1 into @FirstName,@email
end
close c1
deallocate c1
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply