Home > IT Answers > Database > Schedule SQL job to run and export to CSV file
Sesa
5 pts.
 Schedule SQL job to run and export to CSV file
CSV, SQL Jobs, SSMS 2008
Hi. I am trying to create a SQL job to run every hour and export results to a CSV file. I have created the job and scheduled it but can not figure out how to have it create the CSV file. Thanks!

Software/Hardware used:
SSMS 2008
ASKED: February 23, 2012  11:45 PM
UPDATED: May 8, 2013  2:45 PM
RELATED QUESTIONS
Answer Wiki:
We use BCP: 
set @sql = 'select * from [server].dbname.dbo.tablename'
set @bcp = 'bcp "' + @sql + '" queryout serverpathFileName.csv -T -t, -c'
exec master..xp_cmdshell @bcp
-t, references comma separated values Depending on the version of the BCP utility you may or may not be able to use temp tables.
Last Wiki Answer Submitted:  May 8, 2013  2:45 pm  by  Michael Tidmarsh   13,930 pts.
All Answer Wiki Contributors:  Michael Tidmarsh   13,930 pts. , Smf   170 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

I would use powershell to run your query.

$results = Invoke-sqlcmd -serverinstance "instancename" -database "dbname" -query "query"

$results | export-csv filename -force

That should get you want you want. Just be sure that you have SQL Server 2008 client tools installed and that you are adding the ps snappin in order to use the invoke-sqlcmd cmdlet.

Colin Smith
 845 pts.
 

Use the sp_CreateDelimitedTextFile command.  Was very easy:

EXEC sp_CreateDelimitedTextFile 'name of view I created', 'Full path to .csv file'

And that was it.

 

JSH
 40 pts.
 

Actually, let me elaborate that I put the command inside a procedure that is called when I want to build the file.

JSH
 40 pts.