Sending Query Output Using SQL Server Database Mail

Here i will show how to send SQL server query attachment using SQL Server mail.

In order to send database mail first we need to configure database mail profile and database mail account.

In order to configure datamail go through the post

http://reddy25.blogspot.in/2013/05/how-to-send-database-mail-using-sql.html

Once we have created the database mail profile. we can database mail using the system stored procedure.

sp_send_db_mail -- exists in msdb database.

The below example will send the query attachment as text file.

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Mail Profile'  -- Database Mail Profile name

  , @recipients = 'm.nagamalleswarareddy@gmail.com' --- recipients email id

  , @subject = 'Customer Data'  -- Mail Subject

  , @query = 'select CustomerID,CustomerName from DatabaseMirroring.dbo.customers' -- query

  , @attach_query_result_as_file = 1  --- 1 query result attached as file , -0 means query results will not attached

  , @query_attachment_filename = 'CustomersData.txt';  --- attachement name

select *  from msdb.dbo.sysmail_sentitems where subject = 'Customer Data'

select * from msdb.dbo.sysmail_attachments where filename = 'CustomersData.txt' 

sysmail_sentitems will shows the delivered mails

sysmail_attachments will shows the delivered the attachments.

Execute the above query to test db mail. 

Open mail to see the mail delivery

open customer data

the attachments send properly. the text is not in readable format. In order to send the output format as table we have to use HTML tags. The below query will send the query result in HTML format.

TR- Table Row
TD - Table data
TH - Table header

Declare @Body varchar(max)

SET @Body = (SELECT td = CustomerID,'',td = CustomerName ,''

FROM Customers

FOR XML RAW ('tr'),ELEMENTS)

SET @Body = N'

Customer Details

' +

                        N'' +

                        N' Customer ID Customer Name '+

                        +@Body+N'

'

                       

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Mail Profile'  -- Database Mail Profile name

  , @recipients = 'm.nagamalleswarareddy@gmail.com' --- recipients email id

  , @subject = 'Customer Data'  -- Mail Subject

  , @body = @Body

  , @body_format = 'HTML' 

The recipient output look like this