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.

(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;
SELECT Sno,Name,Age,code FROM Student

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 = '********',
@query = 'USE SqlTutorials;
SELECT Sno,Name,Age,code FROM Student
@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 = '********', 

@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

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window

 5,347 total views,  16 views today

Leave a Reply

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