Microsoft SQL Server Front End Blog

The writers of this blog are a part of the Microsoft SQL Server Manageability team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Agent, Data collector and other tools

SQL Database Mail - Send T-SQL Results by Email

SQL Database Mail - Send T-SQL Results by Email

  • Comments 8

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 8 and 6 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

Page 1 of 1 (8 items)