SQLServer (both express and full versions) provide a useful command line tool (sqlcmd) which you can use to run batch tasks. See the full list of options here. Here's a quick way to get usable CSV output from this tool.
The main things to watch out for are specifying the correct delimiter.
- -s: For Comma-Seperated-Values (CSV), the value of -s ","works a joy.
- -W: The -W option removes the trailing whitespace.
- -h: Setting this to -1 removes the headers of the fields (if your query is returning a result set).
- -i: This is the input file (the file with the sql to be executed). For interactive scripts and to make your batch file resuable, set this to %1 to use the first command line argument
- -o: This specifies the output file
- -S: The server.
- -U: The user
- -P: Password
The complete version of the reusable script with comments and usage:
I'm still working on how to remove the number of rows affected line after the result set.
UPDATE: Setting NoCount On (Set NotCount On) in the SQL file disables this behaviour.