Monday, March 11, 2013

Sending Query Results To Email Using SP_Send_DbMail

Sending T-SQL Results as attachment mail

EXEC msdb.dbo.sp_send_dbmail
    @recipients=N'hello@gmail.com',
    @body='Query Results Are Attached',
    @subject ='Query Results',
    @profile_name ='Kalyan_Notifications',
    @query ='select name, createdate from master..syslogins',
    @attach_query_result_as_file = 1,
    @query_result_separator =',',
    @query_result_no_padding=1,
    @query_attachment_filename ='QueryResults.txt'
Sending Stored Procedure Results as attachment mail

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'hello@gmail.com',
@body='Query Results Are Attached',
@subject ='Query Results',
@profile_name ='Kalyan_Notifications',
@query ='exec usp_getLoginInformation',
@attach_query_result_as_file = 1,
@query_result_separator =',',
@query_result_no_padding=1,
@query_attachment_filename ='QueryResults.txt'


@recipients - senders address, Ex:@recipients =hello@gmail.com;hello1@gmail.com
@body -- body of the message
@subject  -- subject line of email
@profile_name -- Mail Profile name, if you don't specify profile it uses default profile name
@query -- A valid T-SQL Statement
@attach_query_result_as_file -- It is used to control whether the result of the query will be attached as file or included in the body of the email.
@query_result_separator -- It is used to seperate the columns in the query output.
@query_result_no_padding -- Default value is 0, when you specified 1 the query results are not padded.
@query_attachment_filename -- Name of the attachment file

 

3 comments:

  1. @query='
    SET NOCOUNT ON;
    select ''sep=;''
    select ''Col1'',''Col2'',''Col3'',''Col3''

    select CONVERT(NVARCHAR,Col1),ISNULL(Col2, ''''),Col4
    FROM ...
    SET NOCOUNT OFF;
    ',

    --Additional settings
    @query_attachment_filename = '*.csv',
    @query_result_separator = ';',
    @attach_query_result_as_file = 1,
    @query_result_no_padding= 1,
    @exclude_query_output =1,
    @append_query_error = 0,
    @query_result_header =0;

    ReplyDelete
  2. This is fantastic, exactly what I was looking for. Thanks.

    ReplyDelete