'Automated Export to CSV Using SQL Server Management Studio

Using Microsoft SQL Server Management Studio, I have created a view, which pulls in columns from several tables. I need to export this view into a CSV file on a weekly basis, and so I would like to set up some sort of automated process for this. I have read many examples of how I can do a simple right click and "Save Results As", or using the export wizard, but I do not know how I can automate this process to run weekly.

I am somewhat of a newbie with all things microsoft, so any help is much appreciated, thanks!

sqlsql-serversql-server-2008


Solution 1:[1]

It is also easy to setup a mailer that would mail a csv file as an attachment on a weekly basis using sp_send_dbmail command.

As a CSV attachment:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'AdventureWorks2008R2 Administrator',
    @recipients = '[email protected]',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2008R2.Production.WorkOrder
                  WHERE DueDate > ''2006-04-30''
                  AND  DATEDIFF(dd, ''2006-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

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

Solution 2:[2]

You can create an SSIS package (just google "sql server export data ssis") and execute it each week (you can create an automated task for this) or you can use xp's like demonstrated in this article.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Denis Valeev
Solution 2 octopusgrabbus

Related Questions