Home » How to send a SQL Server query results in e-mail message

How to send a SQL Server query results in e-mail message

How to send a SQL Server Query result in e-mail message?




If you have configured SQL Server Database Mail then you can easily send query result as an attachment in mail body.

If you do not know how to configure database mail for first time then you can read the post

How to Configure SQL Server database Mail.

Once you configured Database mail then you need to go through following steps.

First we create a SQL Server table named as student then will insert some dummy records into table.

After that we will execute SQL Server database mail stored procedure inorder to send student table records into mail in text file.

CREATE TABLE Student
(Sno INT IDENTITY(1,1), Name VARCHAR(50), Age INT, Code INT)

INSERT INTO student VALUES ('Rakesh' ,19,4567)
INSERT INTO student VALUES ('Suresh kumar' ,18,4599)
INSERT INTO student VALUES ('Manoj singh' ,21,400)
INSERT INTO student VALUES ('Harish Tejasvi' ,20,34546)

Now we have a table student in SQL Server database, to fetch the records from student table use following query.

USE SqlTutorials;
GO
SELECT Sno,Name,Age,code FROM Student
GO

As you can see, it returns all the records fro student table.

Now our requirement is to send this query result in .txt file as an attachment in mail.

To send query result in mail, we will specify the above query into Database Mail Stored procedure in @query parameter as followings.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseMailAlertProfile',
@recipients = '********@gmail.com',
@query = 'USE SqlTutorials;
GO
SELECT Sno,Name,Age,code FROM Student
GO',
@subject = 'Student Registrations',
@attach_query_result_as_file = 1;

 





Once you execute above database mail stored procedure, an email is sent to user’s mail Id as shown in below screenshot.

When you open attached file in mail, you can see student records which are output of query .

 

You can also add a message in mail body, passing value to @body parameter in stored procedures as shown below.

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DatabaseMailAlertProfile', 

@recipients = '********@gmail.com', 

@query = 'USE SqlTutorials; 

GO SELECT Sno,Name,Age,code FROM Student GO', 

@subject = 'Student Registrations', 

@body='provide mail body message' @attach_query_result_as_file = 1;

 

 

SQL Server Database Mail Configurations

Troubleshooting Database Mail in SQL Server




 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.