schedule export SQL query to csv with custom name

Question

I was able to write a SQL query on a SQL Server 2008 database to show the data I need. Now I need to figure out how to export the query into a csv file automatically every night with the date of the export as the file name.

My research tells me there are two ways to do it. One is using task scheduler to run a query file, or to use the server agent. Unfortunately I'm not able to get to agent working so I'll have to figure out how to write a query that will do what I need.

Are you versed in PowerShell? You could write a small script that connects to the database, runs the query, puts the results in a CSV format and writes it to a file. Then you can schedule the execution of the task in the Windows Task Scheduler or any other scheduling system you or your client has. Here's one example I found: billfellows.blogspot.com/2011/03/… — Cᴏʀʏ
– Cᴏʀʏ, Commented Apr 14, 2016 at 21:58

dbbri · Accepted Answer · 2016-04-14 22:02:37Z

1

DOS batch script, then schedule the task to run it.

set mydate=%date:~6,4%%date:~3,2%%date:~0,2%
sqlcmd -U UserName -P Password -S ServerName -Q "SELECT * FROM TABLE" -o "File_Name_%mydate%.csv" -s","

answered Apr 14, 2016 at 22:02

dbbri

2241 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sean W · Accepted Answer · 2016-04-16 16:05:15Z

1

Thanks to the powershell script link posted by CORY, I modified the script to access my database and then used (Get-Date).AddDays(-1).ToString('MM-dd-yyyy') to get the yesterday's date in string, then put it all together for the Export-CSV file path.

I'm going to spend some time exploring Powershell to see what else it can do.

answered Apr 16, 2016 at 16:05

Sean W

4071 gold badge6 silver badges18 bronze badges

Collectives™ on Stack Overflow

schedule export SQL query to csv with custom name

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related