SQL Server Agent is one of my favorite tools – it allows you to automatically schedule jobs, alert you if things are going badly, and capture information into database tables.
One common request is to execute a query and send the results via email on a regular basis. This task is a great match for SQL Server Agent and sp_send_dbmail. Setting up a job and a step that uses sp_send_dbmail with the @query parameter, and building on it with other available parameters, you can easily accomplish this.
Let’s take a look at a job I’ve created. The first step is to gather metrics from the sys.dm_os_sys_memory DMV. I insert the data into a table in my master database.
The second step in this job uses sp_send_dbmail to send the results of the query from today to a database mail profile account.
Let’s look at the command more closely.
|
1 2 3 4 5 6 7 8 9 10 |
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Main DB Mail profile', @recipients = 'jes@brentozar.com', @subject = 'Memory Values', @query = N'SELECT total_physical_memory_kb, available_physical_memory_kb, system_memory_state_desc, collection_date_time FROM MemoryDMVHistory WHERE CAST(collection_date_time AS Date) = CAST(GETDATE() AS DATE) ORDER BY collection_date_time DESC;', @attach_query_result_as_file = 1, @query_attachment_filename = 'Memory Values.txt' |
- @profile_name is the Database Mail profile you want to use to send the email. You must have at least one profile already set up.
- @recipients is the list of email addresses to send the email to. If you have more than one, separate them with a semi-colon.
- @subject is the email subject.
- @query is the query you wish to run. MSDN says it “can contain any valid Transact-SQL statements”, but I haven’t tested the limits of this statement yet.
- @attach_query_results_as_file has two options: 0 or 1. When set to 0, the query results are included in the body of the email. When set to 1, the results are included as an attachment.
- @query_attachment_filename is optional, and is the name and file extension of the attachment. I recommend setting this, with a .txt or .csv extension.
There are other options you can add as well, such as @execute_query_database, @query_result_header, @query_result_width, and more.
When I execute the job, I receive this email:
When I open the .txt attachment, I will see my query results:
This isn’t well-formatted, so I may want to modify the results with @query_result_header and @query_result_width.
Both SQL Server Agent jobs and sp_send_dbmail give you a lot of flexibility for creating and emailing query results. Learn to use them effectively!
69 Comments. Leave new
If you’re looking for something more readable, you can also use the query XML functions to create a HTML document (either inline or as an attachment) instead of a TXT file.
To do so, you would want to include before and after your query results, FOR XML PATH( ‘tr’ ), and specify that each of the result fields should be placed in a td XML node (i.e. td = total_physical_memory_kb in the SELECT predicates).
The BOL page for sp_send_dbmail basically lays out the structure and syntax to send a table as HTML. I use it all the time to send various reports to people who never read them.
http://msdn.microsoft.com/en-us/library/ms190307.aspx
I know that feel, bro. Hours and hours developing and testing awesome reports that will never be read.
I also send out HTML reports using SQL Agent. Does anyone know of a good HTML email template? My reports look a bit boring and I’d like them to stand out a bit more.
Hi,
I tried above query got a question ,what if we want send 2 or 3 query results in different excel sheets .
Please let us know.
Thank you
I have done like this
DECLARE @Table NVARCHAR(MAX) = N”
DECLARE @tableHTML nvarchar(max)
SELECT @Table = @Table +” +
” + CAST(total_physical_memory_kb AS VARCHAR(100))+ ” +
” + cast(isnull(available_physical_memory_kb,”)as varchar(100)) + ” +
” + isnull(system_memory_state_desc,”) + ” +
” +cast( isnull(collection_date_time,”) as varchar(100)) + ” +
”
FROM
MemoryDMVHistory
SET @tableHTML =
N’Memory Values’ +
N” +
N’
total_physical_memory_kb
available_physical_memory_kb
system_memory_state_desc
collection_date_time
‘ + @Table + N”
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘DataMail’,
@recipients = ‘shaul@017.net.il’,
@subject = ‘Memory Values’,
@body = @tableHTML,
@body_format = ‘HTML’ ;
is it possible to embed a date in the file
Hi,
Long time listener, first time caller. I’m playing around with this ability and found that if you have a string in your query, you’ll get a syntax error. I was looking for string ‘Null’ values and in another idea tried to return searched strings from my error log using this method.
I may very well be the problem and not this functionality, DBA for less than 5 months, so I’m just playing around with it.
Any thoughts?
Answered my own question, double quotes and glasses to make sure you don’t have too many other characters.
Hi, new dba, thanks for the article! I have written a small script inserting data in a global temp table which is then emailed via sp_send_dbmail. The results I get back in email contains the data from the global temp table but also inserts random parts of the script in with it. So I get a few rows of data, a few lines of script, more data rows, more script, etc. Any idea of why I’m getting parts of the script listed in the email?
Thank you!
Joey – unfortunately, troubleshooting your script is a little outside of the scope of something we can do in a blog post comment. 😀
Sorry, I found the issue too. Wish I could take the post back. :-/
Hint: If your query returns results but when you place the code in a job and the email contains no results: check that the “Log On As” user for the SQL Server Agent has permission to send email.
I also use SQL Server Agent and sp_send_dbmail to send outstanding tasks to employees formatted in html-code.
How do i prevent sending the e-mail when the result is empty?
Now the employees are receiving empty mails..
Adrie – use COALESCE or ISNULL to give yourself a default value for a nullable field, or an IF NOT EXISTS query to check for no result set.
Hi Brent,
Google provided me with another answer 😉
I added the following statements;
IF (select count(*) from dbo.tabel where “search statements”) > 0
BEGIN
“existing SP_SEND_DBMAIL statements”
END
Great, this helped me out! Thanks!
Any way to make a part name dynamic? ie. add a datetimenow() to the text of the subject, body, or filename.
Susan – sure, you totally could. Going into string-building is a little beyond what I can do fast in this comment, but feel free to add a question about techniques over at http://dba.stackexchange.com.
Here is something I am using running backup on sql express edition
declare @database as nvarchar(30)=’GoldMine’
declare @backuppath as nvarchar(512)=’C:\SQLBACK’
declare @Tstart as datetime=getdate()
declare @Tend as datetime
exec [GoldMine].dbo.DatabaseBackup @database,@backuppath
–send completion email
declare @ServerName varchar(128)=@@SERVERNAME
declare @email varchar(80)=’XXXX@XXXXXXXX.net.il’
declare @body nvarchar(4000)
set @Tend=getdate()
declare @s as int=datediff(s,@Tstart,@Tend)
–Computing the duratin of the backup in days,HH:MM:SS format
declare @duration as varchar(30)=
CONVERT(VARCHAR(12), @s /60/60/24) + ‘ Day/s, ‘
+ CONVERT(VARCHAR(12), @s /60/60 % 24)
+ ‘:’ + RIGHT(‘0’ + CONVERT(VARCHAR(2), @s /60 % 60), 2)
+ ‘:’ + RIGHT(‘0’ + CONVERT(VARCHAR(2), @s % 60), 2)
SET @body = ‘
Database Backup has completed on ‘ + @@SERVERNAME + ‘ on ‘ + cast(getdate() as varchar(20)) + ‘.
Backup was set in ‘ + @backuppath + ‘\’ + @database + ‘
Backup duration was ‘ + @duration + ‘
Thank you,
Shaul
DBA
CompaanyName
XXX-XXXXXXXX>
XXXXX@XXXXXX.net.il
‘
–send Email report———-
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘DataMail’,
@recipients =@email,
@subject =’AUTOMATED SEND: GOLDMINE Database backup has been completed’,
@body =@body,
@body_format =’HTML’;
Performance Tuning – MS SQL
We want to know the list of trouble shooting steps to be performed for DB performance related issues in MS SQL
Thanks in advance.
Gokul – that’s not even remotely related to the blog post you’re commenting on. This isn’t how the Internet works.
MS SQL DB Mail – Creating a SQL job to send output of a query in a spreadsheet
We need to generate a report every week for the list of alerts we received from a SQL DB. Instead of manually pulling the report, we are looking for a solution which will automatically mail the report to designated users in a spreadsheet format.
Thanks in Advance
OK, great. Let us know what you find.
^ handled like a pro
Hahaha, thanks, sir. Sarcasm-as-a-Service, just another free thing we provide here at Brent Ozar Unlimited®.
Yes, that is what IT really needs more of, sarcasm. The woods are full of smart a** IT techs, who I will never hire. Soft skills are a must, ask around.
What do you mean by that?
Ah, you’re only interested in the dumb ass IT techs. Got it.
Dang. I was really hoping I could come to work for you, Anonymous Internet Commenter. My heart, it is full of breaks. The whole reason I’d started my own company was to achieve the dream of full time employment working for someone who is too strong to leave their name in a blog post comment.
My dreams, they are dashed.
Erik, Richie, and Tara, the company is yours. I shall throw myself out of the nearest window.
Sarcasm is great, until it’s pointed at you? Ask you customers, perhaps confidential surveys, you will see the truth. Our industry has been crippled by attitudes, not intellect. BTW, my email was attached to my response.
Sure, read about the confidential survey results: https://ozar.me/tag/presenting/
And I actually love sarcasm pointed at me. It’s a hoot.
We definitely need MORE people in the world that DON’T take themselves (or others) so seriously. Maybe this guy needs an emotional support dog? I, for one, need a little more levity in my life – not less. Keep on keeping on Brent
I have run this many times successfully but now am trying to run a query that is too large. The email is queued but the email is being returned/bounced back by outlook saying the file size is too large. Without getting our company admin to make changes any ideas on getting the file size smaller? It is appropriate sized if I run the query outside of the msdb email job.
Susan – sure, you could return less results.
Thanks. Not an option as the client wants one file. Any other ideas? Anything welcome. I’m thinking outside the box at this point.
Thank you for the helpful article. I’d like to add one thing that had me stumped for a while. My job was failing with “Error formatting query, probably invalid parameters” even though the command worked fine when executed from a query window.
In my case, I had to give the service account running SQL Agent explicit permissions to the target database. Might be obvious to some, but as a SQL Server newbie it took me a little while to figure this out.
I have a report that pulls data for the previous month. Currently my second step runs a batch file that renames the file and adds the current date (Job runs on the first of every month) so my user knows the data is for the previous month. Is there a way to accomplish this with dbmail?
The batch file also copies the renamed file to a share folder but I would prefer the ability to email the report.
Mike – for general questions, your best bet is http://dba.stackexchange.com.
Hi i got task to genrate automatic mail in sql server 2008 r2 the mail should automatically give you information about your drive space .
please help me into this
Sonu – your best bet there is to check out monitoring tools like Dell Spotlight, Idera SQL Diagnostic Manager, and SQL Sentry Performance Advisor.
I’m receiving “Failed to initialize sqlcmd library with error number -2147467259.” on SQL Server 2014 SP1 when running anything related to sp_send_dbmail. Any ideas?
No, for personalized error troubleshooting, head on over to http://dba.stackexchange.com.
@recipients = ‘asdasd@mail.com’,
@execute_query_database = ‘databasename’,
@subject = ‘Memory Values’,
hello,
I have created an job for sending emails .And my query results are correct and everybody is receiving emails except one person . I can see that person email address from query but when my automated job sends email only one person in the emaillist wont receive that email. Can some body answer this.
Sure, head on over to http://DBA.StackExchange.com and post as much detailed information as you can.
Is there script like the ‘EXEC msdb.dbo.sp_send_dbmail’ script that saves the results of the job to a specified folder instead of emailing it?
That sounds like a great question for Dba.stackexchange.com – head on over there.
This was very helpful, thank you for sharing your knowledge in such a clear and well written post!
We send report using this method but due to smtp issue email part failed. Now is there any way to send email with result that was generated during that failure? Because after that this job ran successfully many times and user wants data generated during the time of failure.
Does result get cached somewhere?
Bhargav – for questions, head on over to http://dba.stackexchange.com.
Even before using the agent this is what I get in SQL2014 SP1 when executing as a sys admin on instance and server 🙁
Msg 22050, Level 16, State 1, Line 0
Failed to initialize sqlcmd library with error number -2147024809.
Phillip – if you Google for that, it’s a fairly common error:
http://stackoverflow.com/questions/29501341/generic-failure-using-sp-send-dbmail-in-sql-server-2014
take a look at vsql-email (sql-email.com), is very simple to use
thank you, very helpful!
Is it possible to eliminate the rows of dotted lines that precede the query in the body of the email–without using HTML?
Sorry I asked the question too soon, I found my answer. The solution that works for me to suppress the dashed lines was to add @query_result_header = 0 Reference: https://msdn.microsoft.com/en-us/library/ms190307.aspx
Just out of curiosity, could the results of the query be emailed in xlsx format?
Buenas -Tardes
ya lo he realizado el tema que presento es que el archivo es muy grande y es en formato .csv pero cuando trato de enviarlo .txt no se envia saben que podria ser?
when insert/Modify a row in SQL table, we need to send mail and update the Status as “EMAIL SENT” and Date too in same row.
It’s alluded to at the very end of the article but took me a while to figure out so if you happen to notice that your query results are wrapping across multiple lines when they shouldn’t be, you can use @query_result_width to prevent that from happening. The default value for this appears to be 255.
Do yourself a favour here:
DECLARE @tab char(1) = CHAR(9)
…
,@query_result_separator=@tab –enforce tab-delimited, since it really wants to do this by default
,@query_result_no_padding=1 –trim
,@query_result_width=32767 –stop wordwrap
,@query_attachment_filename = ‘yourfile.csv’
PERFECT, 2 Thumbs up Kori
It was actually a treat for myself. You made my day 🙂
Brent,
I’ve been looking around for a way to use an SP to return a resultset and then mail it, but have not found an example. Please pass along anything that can point me in that direction.
Ted – for questions, head to a Q&A site like https://dba.stackexchange.com.
Hello – I have a literal string in my @query. When I run the query just in the database, it returns the correct results. When I run it in the job it always returns the “PRINT”.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLAdmins’,
@recipients=’a@b.com’,
@subject = ‘New User Created Check’,
@query = N’If (select count(*) from sysusers where createdate > getdate()-1) = 0 PRINT ‘No new users created’ ELSE (select name, createdate from sysusers where createdate > getdate()-1);’,
@attach_query_result_as_file = 0
What do I need?
This is brilliant. I just proofed this out locally to send me PLE metrics and next I’ll be setting this up on a customer’s server to poll PLE intermittently.
Perfect!! I can send out daily numbers in a simple job.
Hi,
I have multiple servers. All servers have same job. Is there any way I can combine result from all server and send in single email instead of multiple email?
Jolly – for general Q&A, head to a Q&A site like https://DBA.StackExchange.com.