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…
- Create a c:\utils folder to store your files
- 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.
- 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.
@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
set nocount on; SELECT * FROM DOCUMENT WHERE DOCUMENT.ID = '123'