In last post, TIP#96 We have configured Database E-mail. Now in this post we will see how to send mail in SQL SERVER.
Sometimes we need to send mail for different requirements like
1) Notification mails like data inserted /updated/deleted successfully
2)Send data reports like no of amount earned, failed transaction etc.
for this SQL SERVER provides us stored procedure “sp_send_dbMail”.
The “SP_send_dbmail” has various parameters below are some important parameters like
@profile_Name : The profile which will use to send mail
@Recipients : To whom the mails need to be send
@body : This is message body
@Subject: Subject of the mail
@Query : SQL statement which you want to share
To send a simple E-mail we can write following statements
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘IndiandotnetMailSmtp’,
@recipients = ‘rajatjai@gmail.com’,
@body = ‘Mail sent successfully.’,
@subject = ‘Mail via SQL SERVER ‘ ;
We can also send a query result either inline text format or html format or as a attachment
Below query will send mail to me with count of students of a class. the below mail is simple text format mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘IndiandotnetMailSmtp’,
@recipients = ‘rajatjai@gmail.com’,
@query = ‘SELECT COUNT(1), Class FROM IndiandotnetDB.dbo.tblStudent GROUP BY class’ ,
@subject = ‘Mail via SQL SERVER ‘ ;
The above mail can also be send mail as a attachment with following command
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘IndiandotnetMailSmtp’,
@recipients = ‘rajatjai@gmail.com’,
@query = ‘SELECT COUNT(1), Class FROM IndiandotnetDB.dbo.tblStudent GROUP BY class’ ,
@subject = ‘Mail via SQL SERVER ‘ ,
@attach_query_result_as_file = 1 ;
We can add @body_format = ‘HTML’ and use various HTML tag in query or body parameters
A part from this you can cross check E-mail status whether mail is sent or not if it is not sent the what is the reason.
Below are the statements which can help to cross check mail sent status
— Show all the emails
SELECT * FROM msdb.dbo.sysmail_allitems
— Show all the sent mails
SELECT * FROM msdb.dbo.sysmail_sentitems
— show all the un sent mails
SELECT * FROM msdb.dbo.sysmail_unsentitems
— show all the failed mail with reason
SELECT * FROM msdb.dbo.sysmail_faileditems
I hope this might help you somewhere.
Enjoy!!
RJ
