Microsoft SQL Server on Windows Azure Virtual Machines

The writers of this blog are a part of the Microsoft SQL Server team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Server components on Windows Azure Virtual machines

SQL Database Mail - Send T-SQL Results by Email

SQL Database Mail - Send T-SQL Results by Email

  • Comments 18

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

Leave a Comment
  • Please add 1 and 8 and type the answer here:
  • Post
  • 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'

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

  • 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 Lucas: Users is a table in msdb database?

  • Nice article,  thanks for sharing

  • hi,

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

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

  • 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

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

  • 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

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

  • Works Great!!

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

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

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

Page 1 of 2 (18 items) 12