Often DBAs get requests to email the results of a query attached to a csv file where the query is running from a SQL Server Agent job. This can be achieved through send email task transformation from SSIS or by passing the query as a parameter to sp_send_dbmail stored proc. In this post I am passing the actual query to sp_send_email stored proc to send out an email. Below is the code used to send the results attached to a csv file from the SQL Server Agent.
EXEC msdb.dbo.sp_send_dbmail @profile_name = '<Your DB Mail Profile Name>', @Query = '<your query>', @attach_query_result_as_file = 1, @query_attachment_filename = 'your filename.csv', @query_result_separator = ' ', @query_result_no_padding = 1, @query_result_width = 9999, @subject = 'Email Subject Name', @body = 'Your email body text', @recipients = 'email@example.com;firstname.lastname@example.org', @copy_recipients = 'email@example.com', @body_format = 'text'
The stored proc uses the database mail profile to send the email. The columns selected in your query are seperated by the delimiter ‘ ‘.
Hope this helps.