Mustafa EL-Masry Database mail

How to send Email With Tabel Foramt by Database Mail


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

   

Mustafa EL-Masry Database mail

How to use Database Mail for sending emails with MSSQL


In the first you must read this Article { https://mostafaelmasry.wordpress.com/2012/01/31/how-to-configure-database-mail-in-sql-server/ }

Sending a test email

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘Main’,

@body = ‘Artsi Fartsi…’,

@subject = ‘Blah Blah’,

@recipients = ‘junk@sqlhacks.com’

go

Email sent verification

SELECT CAST(subject AS VARCHAR(20)) AS ‘Subject’, CAST(body AS VARCHAR(20)) AS ‘Body’, send_request_date, CAST(send_request_user AS VARCHAR(20)) AS ‘Sent by’, sent_account_id, sent_status, sent_date FROM msdb.dbo.sysmail_allitems;. go

Emailing a query result

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘Main’,

@body = ‘Here it is…’,

@subject = ‘SQL query’,

@recipients = ‘junk@sqlhacks.com’,

@execute_query_database = ‘sql911’,

@attach_query_result_as_file = 0,

@query = ‘select sales_date,sales_qty,sales_price from sales;’

go

SELECT CAST(subject AS VARCHAR(17)) AS ‘Subject’,

send_request_date,

CAST(send_request_user AS VARCHAR(20)) AS ‘Sent by’,

CAST(query AS VARCHAR(55)) AS ‘Query’,

sent_account_id, sent_status, sent_date FROM msdb.dbo.sysmail_allitems;

go

Mustafa EL-Masry Database mail

How To Configure Database mail In SQL Server


introduction for database Mail in SQL Server 2012

Today I will explain how to configure database mail in SQL Server 2005,2008,2012 , No difference between this version in the database mail configuration , database Mail is good Option in SQL Server because very easy in the Configuration and its very helpful I think ALL DBA use this option because this option he can Follow up his database . if you want to configure Database Mail you have to ways one is the Wizard ant the Way no Two is Coding and i like this way more than the wizard so let me sow to you how can you Configure Database Mail in SQL Server 2012 by Coding *** I say SQL Server 2012 because when I write this Article i make this Example in my SQL Server 2012 ****

Configure Database mail In SQL Server Steps

Step No one [Enable database Mail option]

use master

go

sp_configure ‘show advanced options’,1

go

reconfigure with override

go

sp_configure ‘Database Mail XPs’,1  —— Enable database Mail

–go

–sp_configure ‘SQL Mail XPs’,0 ——- Disable database mail

go

reconfigure

go

Step No Two [Create database mail Account]

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = ‘safeerp’,

@description = ‘Mail account for Database Mail For safeerp Company Created By Mr/ Mostafa Elmasry’,

@email_address = ‘Elmasre_201038@Yahoo.com‘,

@display_name = ‘Database Mail SQL Server 2012′,

@username=’Elmasre_201038@Yahoo.com’,

@password=’Email Password’,

@mailserver_name = ‘smtp.mail.yahoo.com’

 Step No Three [Create database mail profile]

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = ‘safe’,

@description = ‘Profile used for database mail safeerp Company Created By Mr /Mostafa Elmasry’

Step No Four [Add the database mail account on the profile]

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = ‘safe’,

@account_name = ‘safeerp’,

@sequence_number = 1

Step No Five [Database mail profile Secuirty]

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = ‘safe’,

@principal_name = ‘public’,  ——- you can write here Puplic or Private (Puplic) any User in SQL Server have permision to use the database mail but (private) the User Create he only can use the database mail.

@is_default = 1 ;

Final Step No Sex [Send Email by DataBase Mail SQL Server]

declare @body1 varchar(100)

set @body1 = ‘Server :’+@@servername+ ‘ My First Database Email ‘

EXEC msdb.dbo.sp_send_dbmail @recipients=’elmasre_201038@hotmail.com’,

@subject = ‘My Mail Test From Daem DataBase Mail’,

@body = @body1,

@body_format = ‘HTML’ ;

Or

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘safe’

,@recipients = ‘elmasre_201038@Hotmail.com’

,@from_address = ‘Elmasre_201038@Yahoo.com’

,@subject = ‘Mostafa sql server’

,@body=’My Friend Kemo  now i try to send new Email from my Databse mail Account’

,@query = ‘SELECT * FROM msdb..sysmail_allitems WHERE sent_status =”sent” ‘

/*

When you want to send mail like step no 6 you must learn the pramter to send it to the procedure [msdb.dbo.sp_send_dbmail] so lets check this pramter in this link http://msdn.microsoft.com/en-us/library/ms190307.aspx

*/

Now after you send the database mail sure you want to check the status for this mail so run tis script to check the mail status

SELECT * FROM msdb.dbo.sysmail_event_log

order by log_date

use msdb

exec sysmail_start_sp

GO

SELECT * FROM msdb..sysmail_allitems WHERE sent_status = ‘sent’

Database Mail Configuration