SQL Database Mail – Send T-SQL Results by Email

This post demonstrates how you can have T-SQL results being sent to you in emails by using SQL Database Mail.

The article here is assumed that a SQL Database Mail profile "MailProfile1' has been created.  The MailProfile1 tells Database Mail what account it should use to send emails.  For how to create a Database Mail profile, you can refer to the previous post  SQL Database Mail.

To send T-SQL results by emails, run T-SQL sp_send_dbmail as follows as an example. 

-- Start T-SQL

    USE msdb
    EXEC sp_send_dbmail
      @profile_name = 'MailProfile1',
      @recipients = 'someone@microsoft.com',
      @subject = 'T-SQL Query Result',
      @body = 'The result from SELECT is appended below.',
      @execute_query_database = 'msdb',
      @query = 'SELECT subsystem_id,subsystem FROM syssubsystems'
-- End T-SQL --


This example uses the profile MailProfile1 as your email account and sends T-SQL query result to the recipient email someone@microsoft.com.   The context of email consists of the message @body and the query result.  The entire email will look like below.


    From: Yuhong Li
    Sent: Friday, October 29, 2010 2:16 PM
    To: someone@microsoft.com
    Subject: T-SQL Query Result


    The result from SELECT is appended below.
    subsystem_id subsystem
    ------------ ----------------------------------------
               2 ActiveScripting
              10 ANALYSISCOMMAND
               9 ANALYSISQUERY
               3 CmdExec
               6 Distribution
               5 LogReader
               7 Merge
              12 PowerShell
               8 QueueReader
               4 Snapshot
              11 SSIS
               1 TSQL

    (12 rows affected)

Here is another example that demonstrates how quotation marks and newlines are used to construct the context of emails.


-- Start T-SQL

    USE [msdb]


      @profile_name ='MailProfile1',

      @recipients ='someone@microsoft.com',

      @subject ='T-SQL PRINT Result',

      @body ='The output of PRINT is sent to you as follows.',

      @execute_query_database ='msdb',

      @query = "PRINT CHAR(13)+'Use double-quotation marks.' + CHAR(13) + 'Start a newline'"

-- End T-SQL --



The outcome of email will be shown as follows.

    From: Yuhong Li
    Sent: Friday, October 29, 2010 2:16 PM
    To: someone@microsoft.com
    Subject: T-SQL PRINT Result

    The output of PRINT is sent to you as follows.

    Use double-quotation marks.
    Start a newline


For more information about sending emails, see the syntax of sp_send_dbmail in MSDN documents.

<End of Post>


 This posting is provided "AS IS" with no warranties, and confers no rights. 
Use of included script samples are subject to the terms specified at

Comments (26)

  1. Wallace Houston says:

    I'm trying to execute the script below from SSMS based on your example.  I've tried several changes in syntax, but with the script below I get this error:

    Msg 102, Level 15, State 1, Line 20

    Incorrect syntax near '%'.

    Can you tell me what I'm doing wrong?   Thanx, Wallace

       USE msdb

       EXEC sp_send_dbmail

         @profile_name = 'whouston',

         @recipients = 'someone@microsoft.com',

         @subject = 'Order Complete, Shipment Pending',

         @body = 'Completed Orders with Pending Shipments',

         @execute_query_database = 'msdb',

         @query = 'use SfiData

    select distinct os.OrderNumber, substring(convert(char, cu.customerkey), 1, 7) +

    substring(convert(char, cu.CustomerCheckDigit), 1, 1) as Cuskey,

    oh.OrderStatus, os.ShipmentStatus, oh.OrderDateTime, os.DeliveryDate, os.DateDescription

    into #temp

    from tblOrderShipment as os left outer join tblorderheader as oh

    on os.OrderNumber = oh.OrderNumber left outer join tblCustomer as cu

    on oh.CustomerNumber = cu.CustomerNumber

    where oh.OrderNumber % 100 = (select currentyear from tSystemValues) % 100

    and oh.orderstatus like '%Complete%'

    and os.ShipmentStatus like '%Pending%'

    –and cu.CreditRisk = 0

    group by oh.OrderDateTime, os.DeliveryDate, cu.customerkey, os.OrderNumber,

    cu.CustomerCheckDigit, oh.OrderStatus, os.DateDescription, os.ShipmentStatus

    order by os.OrderNumber

    select * from #temp

    drop table #temp'

  2. Wallace Houston says:

    I just resolved it by putting the script in a stored procedure.  Thanx!

  3. Lucas Otero says:

    Hi Guys,

    I have tried that but I am getting the following error:

    Msg 102, Level 15, State 1, Line 11

    Incorrect syntax near '@execute_query_database'.

    This is query:

       EXEC [msdb]..[sp_send_dbmail]

    @profile_name = 'SQL Server Alert',

    @recipients = 'lucas.otero@ultramartravel.com',

    @subject = 'ALXN Daily Profile Data Update',

    @body = 'NALXN_DailyProfileData file is not available'

           @execute_query_database = 'msdb',

    @query = 'SELECT * FROM Users where email like ''%Lucas.otero%'''

    Any Idea?


  4. Vlad Goia says:

    4 Lucas: Users is a table in msdb database?

  5. Joss says:

    Nice article,  thanks for sharing

  6. JAYADEV SETHY says:


      good evening plz send me  all ruselt plz my mail I d is  jayadevsethy89@gmail.com

  7. Jeshang Gudol says:

    Hi can anyone help me ,,,i am trying to execute below script but throwing error saying

    "Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42

    profile name is not valid"

    — Start T-SQL

       USE msdb

       EXEC sp_send_dbmail

         @profile_name = 'MailProfile1',

         @recipients = 'someone@microsoft.com',

         @subject = 'T-SQL Query Result',

         @body = 'The result from SELECT is appended below.',

         @execute_query_database = 'msdb',

         @query = 'SELECT subsystem_id,subsystem FROM syssubsystems'

    — End T-SQL —


    apperciate yr quick help..

  8. Hi says:

    i have a table(tbl_feedback) data will be updated daily and it contains attachments too. i have to get these database for every week with all the attachments. can any one help me

  9. Esin says:

    Hello, I want to send query result as a table. How can i implamet html or do you know another way?

  10. jun says:

    Hi Yuhong

    I am using SQL2008R2, and I do not have problem to send the database email, my problem is that query result format without linefeed after each row.

    So it returns as a long line from header to "—-" to results.

    How to fix it?


  11. Ali says:

    tried and get Mail Queued  message .. but did'nt receive recipient email ..

  12. Chadwick says:

    Works Great!!

  13. Jed Larry Williamson III says:

    This code gave me exactly what I needed.  You rock!  Tank Ya!

  14. Wong Tong Lou Song says:

    Very good code, helpful, accurate. Thank you very much.

  15. Tidell says:

    Gave me exactly what I needed to get the job done.  Worked like a champ!

  16. Jeramiah Smith says:

    I have been working in IT for a long time.  For the longest time, I have been looking for code to help me with this situation.  I am so happy I found this link.  Many times I have found posted code does not work and stated, in this case however, everything worked on the very first attempt.  Thanks for making my day productive.  This has been a long time needed action from our SQL Server.

  17. Miguel Sontorro Sanchez says:

    Este artigo é incedible. Ele salvou-me a tanto tempo e aborrecimento. Eu aprecio a postagem.  

  18. Constance Winworth says:

    Very very helpful!! Thanks bunches!!

  19. The Zep Fister says:

    Didn't work, can you please provide me some help?  I am getting an error 646, command not performed.

  20. Gonzo Wallace IV says:

    Error 646 can be resolved by recycling the SQL Service after your query is created.  I encountered this error and was able to fix it on 2 of our servers.

  21. Brazzelworth Huntington says:

    Try this:  It works much better!

    — Start T-SQL

       USE msdb

       EXEC sp_send_dbmail

         @profile_name = 'MailProfile1',

         @recipients = 'someone@microsoft.com',

         @subject = 'T-SQL Query Result',

         @body = 'The result from SELECT is appended below.',

         @execute_query_database = 'msdb',

         @query = 'SELECT subsystem_id,subsystem FROM syssubsystems'

    — End T-SQL —

  22. Duffy the Duffster says:

    @query = 'SELECT subsystem_id,subsystem FROM syssubsystems'; (Always place semicolon at the end of a SQL statement)

  23. Kevin KickButt says:

    No you don't have to have a semi-colon there!

  24. Gentle Jasmin says:

    You do too need the semi-colon,,, SQL,,, Hello

  25. Joe Slater says:


  26. Biff Wimsickle says:

    wow, man, this little piece of code sure has generated a lot of conversation since its first post.  GaaaaaaaaDannnnnnnggggggstah

Skip to main content