sqlserver agent job calling winzip

Question

I have a sql agent job that calls a stored proc which runs a select query. This job takes the results from that select and creates a CSV file which I then email. The problem I have is the resulting file is getting too big to email. I've had to modify my email attachment size under the management, but that just solves the problem of creating the file. I still need to email it.  Is it possible to add a step that will zip the file before emailing it?  Here is my code

EXEC msdb.dbo.sp_send_dbmail
@subject = N'Report',
@recipients=N'[e-mail]',
@body = N'Daily Report',
@query = N'V40N05PROD.dbo.CSP_BILLS_STATUS_Export',
@body_format = 'HTML',
@attach_query_result_as_file=1,
@query_result_no_padding =1,
@query_attachment_filename='Report.csv',
@query_result_separator =' ';

Answer 1

Hi wish24bone,

To email a zipped attachment, you are required to export the result to a location by BCP command first, zip it, and attach it via sp_send_dbmail. For stored procedure to zip a file, please pay attention to this sample: Zip One File.

---

Stephanie Lv

TechNet Community Support