MS SQL Database CSV Export – Part 1

By | April 7, 2017

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'

One thought on “MS SQL Database CSV Export – Part 1

  1. Pingback: MS SQL Database CSV Export – Part 2 | Candisa 24x7-9x5

Leave a Reply

Your email address will not be published. Required fields are marked *