How to send E-mail/E-mail with Attachments in SQL Server TIP #97

 

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

Advertisements

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