RSS

How to send Email With Tabel Foramt by Database Mail

22 Dec

Introduction

Any DBA or developer use Database mail Future need to send Email with Special Formate like if you need to send Query from your Database to the Customer but you want to put the result in Table What Will you Do leat’s learn how to send Email with tabel format but in the first you must configure database mail server in youe SQL Server so Read this Article about how to Configure Database mail server http://wp.me/p1Oidq-9A  also you can Check this link now i will do :

1- Create HR_EMP Table

2- Insert Data into HR_EMP Table

3- Define 2 variable @XML to send the Query with XML , @body to bulid the body of tabel with HTML

CREATE TABLEHR_EMP(

[EMP_ID]  int,

[EMP_Name]  Nvarchar(50),

[VISA_Start_Date]Nvarchar(50),

Visa_END_DateNvarchar(50))

INSERT INTO HR_EMP

SELECT 1,‘Mostafa Elmasry’,’18/05/2010′,’18/05/2012′

UNION ALL

SELECT 2,‘Moahmed Osman’,’01/01/2011′,’01/01/2013′

UNION ALL

SELECT 3,‘mahmoude Darwish’,’27/09/2010′,’27/09/2012′

DECLARE @xmlNVARCHAR(MAX)

DECLARE @bodyNVARCHAR(MAX)

SET @xml=CAST((SELECT[emp_id]AS‘td’,,[EMP_Name]AS‘td’,,[VISA_Start_Date]AS‘td’,,Visa_END_DateAS‘td’ FROM HR_EMPORDERBYVisa_END_DateDesc FOR XMLPATH(‘tr’),ELEMENTS)ASNVARCHAR(MAX))

SET @body=‘<html><body><H3>Visa Start Date and End Date to All Employees</H3>

<table border = 1>

<tr>

<th> Employee Code </th> <th> Employee name </th> <th> Visa Start Date </th> <th> Visa End Date </th></tr>’

SET @body=@body+@xml+‘</table></body></html>’

—–Let’s Send Email Now

EXEC msdb.dbo.sp_send_dbmail

@profile_name =‘DBA’,— replace with your SQL Database Mail Profile

@body =@body ,

@body_format=‘HTML’,

@recipients =‘Mostafa@Safeerp.com’,— replace with your email address

@subject =‘Employee Visa information’;

Send mail with table format

   

 
Leave a comment

Posted by on December 22, 2012 in Database mail

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s