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]

    EXECsp_send_dbmail

      @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
http://www.microsoft.com/info/cpyright.htm

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?

    Thanks!!

  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:

    hi,

      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 —

    thnks

    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?

    TIA

  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:

    Neat.

  26. Biff Wimsickle says:

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