Send email from SQL Server Agent with results imported to a csv file

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@email.com;email1@email.com',
@copy_recipients = 'recipients_email@email.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.

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , , , , . Bookmark the permalink.

2 Responses to Send email from SQL Server Agent with results imported to a csv file

  1. AnnLopez says:

    Would it be possible to send this file zipped and password protected??? Thanks in advance.

  2. MoonLightDBA says:

    I havent tried that. But I can say its definitely possible from an SSIS package.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s