Export a flat file daily

I need to create an export txt or csv to run daily and preferably email said file. The data seems doable at 2000-3000 records and a file size under 5mb.
I know how to pull the data into a qry but Im not exactly sure how to automate this to a daily email. I can create an SSRS report and set it up on a subscription but I cant seem to grab all the fields at once to pull into a report (thus making building the report very time consuming.

I would think there is an easier way to create an automated export through SQL
Jeremy KirkbrideAsked:
Who is Participating?
 
bcnagelCommented:
Hi Jeremy.

Take a look at the MSDN description of sp_send_dbmail. I've used this in the past to automatically email the results of a query to one or more interested parties:

https://msdn.microsoft.com/en-us/library/ms190307.aspx 

Example B in the article is especially helpful in your case, I think. It looks like this:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'danw@Adventure-Works.com',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.