Sql Server 2005 , Leave a comment

How To send Email Notification in HTML format in Sql Server 2005

by

I have Sql Server 2005 Database with table holding Clearance data for Organization Employees.HR Department wants to get notification for Employees Clearance information whose Clearance is going to expire within 60 Days. In Sql Server 2005 you can configure database mail Profile to send email.here is good link to configure sql Server 2005 for mail Profile.http://www.mssqltips.com/tip.asp?tip=1100
Now Create Store Procedure to get data for Employees whose clearance will expire within 60 days if there any and Format HTML email and then Use msdb.dbo.sp_send_dbmail Procedure to send email.

CREATE PROCEDURE [dbo].[sp_Clearance_Alert]
AS
BEGIN
DECLARE
@body_message varchar(1000)
IF((SELECT count(*) FROM JM_EMPLOYEE_CLEARANCE_T C INNER JOIN JM_EMPLOYEE_T E ON E.JM_EMPLOYEE_ID = C.JM_EMPLOYEE_ID
INNER JOIN btw.dbo.JM_CLEARANCE_ORGANIZATION_T O ON C.JM_CLEARANCE_ORGANIZATION_ID = O.JM_CLEARANCE_ORGANIZATION_ID
INNER JOIN btw.dbo.JM_AGENCY_CLEARANCE_T A ON A.JM_AGENCY_CLEARANCE_ID = C.JM_AGENCY_CLEARANCE_ID WHERE ABS(DATEDIFF(day,GETDATE(),CLEARANCE_RENEWED_DATE)) = 60) > 0)

BEGIN
SET  @body_message = N'<h4>List of Employees Whose Clearance will Expire in 60 Days.</h4>' +
                                 N'<table style="font-  family: Arial, Helvetica, sans-serif; font-size: 10px;" border="1" cellspacing="0" cellpadding="2" width="100%">' +
                                 N'<tbody><tr valign="top">'+
                                 N'<th align="left">First Name</th><th align="left">Last Name</th><th align="left">Organization</th>' +
                                 N'<th align="left">Clearance</th><th align="left">Issue  Date</th><th align="left">Renew Date</th></tr>' +
                                 CAST((SELECT td = FIRST_NAME, '',
                                                      td = LAST_NAME, '',
                                                      td = CLEARANCE_ORGANIZATION_DESC, '',
                                                      td = AGENCY_CLEARANCE_DESC, '',
                                                      td = SUBSTRING(CONVERT(VARCHAR,CLEARANCE_ISSUED_DATE,101),1,10),'',
                                                      td = SUBSTRING(CONVERT(VARCHAR,CLEARANCE_RENEWED_DATE,101),1,10)
                                                      FROM  btw.dbo.JM_EMPLOYEE_CLEARANCE_T C INNER JOIN btw.dbo.JM_EMPLOYEE_T E ON  E.JM_EMPLOYEE_ID = C.JM_EMPLOYEE_ID
                                                      INNER JOIN JM_CLEARANCE_ORGANIZATION_T O  ON C.JM_CLEARANCE_ORGANIZATION_ID =  O.JM_CLEARANCE_ORGANIZATION_ID
                                                      INNER JOIN JM_AGENCY_CLEARANCE_T A ON  A.JM_AGENCY_CLEARANCE_ID = C.JM_AGENCY_CLEARANCE_ID
                                                      WHERE  ABS(DATEDIFF(day,GETDATE(),CLEARANCE_RENEWED_DATE)) = 60 AND LOWER(EMPLOYEE_STATUS) = 'active'
                                                      FOR XML PATH('tr'),TYPE) AS NVARCHAR(MAX) ) +</tbody></table>';
         EXEC msdb.dbo.sp_send_dbmail
                              @profile_name = 'ClearanceAlerts',
                              @recipients = 'name@domain.com',
                              @subject ='Clearance About to Expire.',
                              @body = @body_message,
                              @body_format = 'HTML';
         END
END

Thanks
Ronak

Leave a comment