Email Query Results Using a SQL Server Agent Job

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.

Job Step 1

Job Step 1

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.

Job Step 2

Job Step 2

Let’s look at the command more closely.

  • @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:

agent job query email

When I open the .txt attachment, I will see my query results:
agent job query text file

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!

Previous Post
Five Oracle Myths
Next Post
The Third Concern for SQL Server Virtualization: Capacity Planning

67 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).

    Reply
  • 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’ ;

    Reply
  • 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?

    Reply
  • Joey Goodson
    May 13, 2015 6:57 pm

    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!

    Reply
  • 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.

    Reply
  • 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..

    Reply
    • 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.

      Reply
      • 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

        Reply
  • Any way to make a part name dynamic? ie. add a datetimenow() to the text of the subject, body, or filename.

    Reply
    • 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.

      Reply
    • 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’;

      Reply
  • 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.

    Reply
  • 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

    Reply
    • OK, great. Let us know what you find.

      Reply
      • ^ handled like a pro

        Reply
        • Hahaha, thanks, sir. Sarcasm-as-a-Service, just another free thing we provide here at Brent Ozar Unlimited®.

          Reply
          • YourResumeIsInTheTrash
            January 9, 2017 3:29 pm

            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.

          • YourResumeIsInTheTrash
            January 10, 2017 8:41 am

            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

        Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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?

    Reply
  • 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.

    Reply
  • 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?

    Reply
  • This was very helpful, thank you for sharing your knowledge in such a clear and well written post!

    Reply
  • 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?

    Reply
  • 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.

    Reply
  • take a look at vsql-email (sql-email.com), is very simple to use

    Reply
  • Is it possible to eliminate the rows of dotted lines that precede the query in the body of the email–without using HTML?

    Reply
  • 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

    Reply
  • Just out of curiosity, could the results of the query be emailed in xlsx format?

    Reply
  • 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?

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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’

    Reply
  • 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.

    Reply
  • 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?

    Reply
  • Jeffrey Mergler
    February 5, 2019 4:26 pm

    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.

    Reply
  • Perfect!! I can send out daily numbers in a simple job.

    Reply
  • 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?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}