Sql server Db Mail which can send mail for two or more queries

Question

I want to send db mail , which contains the result of two queries, how do I make it happen?

USE msdb
EXEC sp_send_dbmail
  @profile_name = 'try01',
  @recipients = 'yyyyy@yyy.com',
  @subject = 'Table Values',
  @body = 'xxxxxxxxxxxxxx.',
  @execute_query_database = 'Reports',
  @query = 'SELECT * from Table'

you can assume any other query for second like select * from table2 both table have a different structure. — Bhaskar Mishra, May 09 '13 at 09:47

score 4 · Accepted Answer · answered May 10 '13 at 09:07

OK, This was easy as if you put a semicolon the result of both the queries are sent. Here it is:

USE msdb
EXEC sp_send_dbmail
  @profile_name = 'try01',
  @recipients = 'yyyyy@yyy.com',
  @subject = 'Table Values',
  @body = 'xxxxxxxxxxxxxx.',
  @execute_query_database = 'Reports',
  @query = 'SELECT * from Table; select * from table2'

Only problem I have now is to get this into a decent looking format.

gbn · Answer 2 · 2013-05-09T09:52:05.513

2

sp_send_dbmail on MSDN shows a single @query parameter
So you can send one query only unless you can use UNION to join result sets.

Or save the query results to disk and then set @file_attachments (I wouldn't, personally)

Or use Reporting Services to query and send the email as a report

edited May 09 '13 at 09:52

answered May 09 '13 at 09:37

gbn

422,506
82
585
676

score 1 · Answer 3 · answered Jun 16 '17 at 15:40

Put your queries into a Stored Procedure and then call it in the query line.

Sample stored proc:

CREATE PROCEDURE build_email_as_query
AS
BEGIN

    SET NOCOUNT ON;

    -- newline var

    declare @lf char(6)
    set     @lf = '<br />'

    -- build the email

    select @lf + @lf

    select 'results 1' + @lf    
    select 'results 2' + @lf    
    select 'results 3' + @lf    

END
GO

exec sp_send_dbmail:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profileName', 
    @body = 'Results of query: <br /><br />',
    @body_format ='HTML',
    @recipients = 'test@domain.com',
    @copy_recipients = '',
    @subject = 'testing emails',
    @execute_query_database = 'DatabaseName',
    @query = 'exec build_email_as_query'

Sql server Db Mail which can send mail for two or more queries

3 Answers3

Linked