Archive

Posts Tagged ‘Sql server; Email’

Sending HTML formated email from sql server

August 16, 2012 Leave a comment

Hi Folks,
After long time jolting something worth having glance. In application development we often come across a typical requirement where we are required to build a utility they will perform certain task(s) on periodic basis. One such type of common requirement is to shot mail(s) on periodic basis.
Recently I built one such utility for auto mailer. There were three (3) possible options to achieve this viz:

  1. Schedule an .exe application which will send emails
  2. Write an window service
  3. Create sql job and execute a sproc which will send email

Of these three options, last option has upper hand, as the data that required to be mailed was in database, also the sender email address and receiver email address were available in db and lastly there is no extra installer required since the job is defined in sql server and hence no deployment hiccups.

But the challenge was to build a dynamic HTML formatted email (challenge: because I underestimated send mail sproc of sqlserver).

But then bit of googling cleared my misconception and then I kick started building the auto mailer.

  • The rough layout of the desired html is as below:

Spot Nomination for July 2012

Emp.ID Employee Name Approver Payroll Amount Currency Mode
1 Joe Gracia Mark twain USA 1000 USD Cash
2 Narayan Jadhav Sam Powel USA 1000 USD Cash
3 Manish Patil Jignesh Patel India 4000 INR Cash
4 Amy Wang Lu Sang Taiwan 2000 TD Cash
5 Tsai Wu Sam Mochi Taiwan 2000 TD Cash
6 Juan Marcos Anna Alvaris Spain 1000 EURO Cash
7 Adongo Wangai Tobe Mugambe Kenya 4000 KES Cash
8 Akae Takahashi Chi Itou Japan 2000 YEN Cash
9 Mohit Gupta Ashutosh Singh India 4000 INR Cash

Thanks & Regards,
Payroll Management Team
paymaster@ttk.mbt.com

  • The Logic for the Stored Procedure
  1. Create a temp table and define necessary columns in it
  2. Populate the temp table by inserting the required data into it
  3. Declare two variables viz @xml NVARCHAR(MAX); @body NVARCHAR(MAX);
  4. Extract the data from temp table, cast it to xml string (essentially with html tags in it) and then assign it to @xml variable
  5. Now this @xml variable is having partial, table formatted data of the body tag
  6. Now build the remaining part of the body tag
  7. At concluding part just execute the msdb.dbo.sp_mail_dbmail stored procedure and provide the @body variable to it
  • The complete sql script is as below:
CREATE PROCEDURE [dbo].[SendEmail_Date_AwardType_Location]
 @FromDate DateTime
 , @ToDate DateTime
 , @AwardTypeID Int
 , @LocationName Varchar(50)
AS

BEGIN
 SET NOCOUNT ON;

CREATE TABLE #SpotNomApporval
(
 [EmpID] INT
 ,[EmployeeName] NVARCHAR(150)
 ,[Payroll] NVARCHAR(150)
 ,[ApprovingAuthority] NVARCHAR(150)
 ,[Amount] INT
 ,[Currency] NVARCHAR(150)
 ,[Mode] NVARCHAR(150)
)

INSERT INTO #SpotNomApporval
SELECT [EmpId]
 ,[EmpName] AS [EmployeeName]
 ,[ApprovingAuthority]
 , AI.[GlobalLocation] AS [Payroll]
 ,[Amount]
 ,[Currency]
 ,[Mode]

FROM [AwardInfo] AS AI
 LEFT JOIN [AwardType] AS AT
 ON AI.AwardTypeID = AT.AwardTypeId
 LEFT JOIN [AwardLocation] AS AL
 ON AI.GlobalLocation = AL.LocationName
WHERE AT.AwardTypeId = @AwardTypeID
 AND AL.LocationName = @LocationName
 AND DateofRequisition BETWEEN @FromDate AND @ToDate

DECLARE @xml NVARCHAR(MAX);
DECLARE @body NVARCHAR(MAX);
DECLARE @duration NVARCHAR(100);

SET @xml = CAST(( SELECT [EmpId] AS 'td'
 ,'',[EmployeeName] AS 'td'
 ,'',[ApprovingAuthority] AS 'td'
 ,'',[Payroll] AS 'td'
 ,'',[Amount] AS 'td'
 ,'',[Currency] AS 'td'
 ,'',[Mode] AS 'td'
FROM #SpotNomApporval
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @duration = (SELECT Month =
 CASE MONTH(GETDATE())
 WHEN 1 THEN 'January'
 WHEN 2 THEN 'February'
 WHEN 3 THEN 'March'
 WHEN 4 THEN 'April'
 WHEN 5 THEN 'May'
 WHEN 6 THEN 'June'
 WHEN 7 THEN 'July'
 WHEN 8 THEN 'August'
 WHEN 9 THEN 'September'
 WHEN 10 THEN 'Octomber'
 WHEN 11 THEN 'November'
 WHEN 12 THEN 'December'
 END) +' '+ CAST((SELECT "Year" = YEAR(GETDATE())) AS VARCHAR(5))

SET @body ='<html><body><span style="color:#17365d"><H3>Spot Nomination for '+@duration+'</H3></span>
<table border = 1>
<tr>
<td bgcolor="#6B696B"> Emp.ID </td> <td bgcolor="#6B696B"> Employee Name </td> <td bgcolor="#6B696B"> Approver </td>
<td bgcolor="#6B696B"> Payroll </td> <td bgcolor="#6B696B"> Amount </td> <td bgcolor="#6B696B"> Currency </td>
<td bgcolor="#6B696B"> Mode </td></tr>'

SET @body = @body + @xml +'</table></body></html>'
SET @duration = 'Spot Nomination for '+@duration

EXEC msdb.dbo.sp_send_dbmail
@body = @body,
@body_format ='HTML',
@recipients = 'manishrao18@gmail.com',
@subject = @duration

DROP TABLE #SpotNomApporval

END

The more helpful resource can be found at below links:
http://msdn.microsoft.com/en-us/library/ms190307.aspx

http://www.mssqltips.com/sqlservertip/2347/send-email-in-a-tabular-format-using-sql-server-database-mail/

Categories: Sql Server Tags: