MS SQL Database CSV Export – Part 1

The following process describes how to connect to a MSSQL database, run a query, and save the result as a CSV file; all via command line so it can be easily added to a batch file and scheduled to run via Task Scheduler or any other mechanism.

In “part 2”, I will describe how to create a simple PHP site to upload your CSV file, or any other file for that matter, to a remote server.

From the server or workstation running MS SQL…

1. Create a c:\utils folder to store your files
2. Create the batch file RunSQL-CSV.bat with the code below
   You can leave the “curl.exe” line out for now, it will be explained in part 2.
3. Create the SQL query file dbquery.sql
   I have added a very simple query here but the query can be as complex as you need.
   The “set nocount on” line will suppress the result count at the end giving you a CSV file that is easier to manipulate.

NOTE: Make sure you are running the batch file as a windows account with access to the database.

RunSQL-CSV.bat

@echo off
c:
cd c:\utils
"c:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S SQLSERVER\DBINST -d dbname -i c:\utils\dbquery.sql -o c:\utils\query_result.csv -W -s"," -w 700 >> runlog.txt
rem curl.exe -F "file_contents=@query_result.csv" http://phpserver/sql-reports/uploader.php >> runlog.txt
echo %date% %time% >> runlog.txt
del /Q query_result.csv >> runlog.txt

dbquery.sql

set nocount on;
SELECT * FROM DOCUMENT WHERE DOCUMENT.ID = '123'