BTerrell Group Blog

Scheduled SQL Server Data Export to CSV using SQLCMD

Posted by Kevin Yu on Tue, Jun 24, 2014

You may recall my blog article last month about using the SQLCMD utility to run a script from the command prompt to restore a database. Ever since then, I’ve found more and more opportunities for the SQLCMD utility to help me manage challenges in Microsoft SQL Server.

This time, the challenge was to generate a scheduled CSV output of data from SQL Server. I could get the data I wanted formatted correctly by using a SQL query and then copy/paste into Excel, but I needed to find a way to have this process done automatically, on a schedule. A little bit of Google-ing led me back to utilizing the SQLCMD utility.

I saved my SQL query that returned the data formatted the way I needed and then entered the text below in the command prompt line:

sqlcmd -S server\instance -i  c:\SQL_Query.sql -o c:\CSV_Output.csv -s”,”

This generated the CSV file, but I still required this process to be scheduled. I knew I could use Windows Task Scheduler to start a program on a schedule I specified, and since SQLCMD is run from the command prompt (cmd.exe), I set up a task to start cmd.exe and used the above text in the add arguments field.

 6 24image1

I’ve included a list of other arguments for your reference you can pass sqlcmd:

   -a packet_size

   -A (dedicated administrator connection)

   -b (terminate batch job if there is an error)

   -c batch_terminator

   -C (trust the server certificate)

   -d db_name

   -e (echo input)

   -E (use trusted connection)

   -f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]

   -h rows_per_header

   -H workstation_name

   -i input_file

   -I (enable quoted identifiers)

   -k[1 | 2] (remove or replace control characters)

   -K application_intent

   -l login_timeout

   -L[c] (list servers, optional clean output)

   -m error_level

   -M multisubnet_failover

   -N (encrypt connection)

   -o output_file

   -p[1] (print statistics, optional colon format)

   -P password

   -q "cmdline query"

   -Q "cmdline query" (and exit)

   -r[0 | 1] (msgs to stderr)

   -R (use client regional settings)

   -s col_separator

   -S [protocol:]server[\instance_name][,port]

   -t query_timeout

   -u (unicode output file)

   -U login_id

   -v var = "value"

   -V error_severity_level

   -w column_width

   -W (remove trailing spaces)

   -x (disable variable substitution)

   -X[1] (disable commands, startup script, environment variables and optional exit)

   -y variable_length_type_display_width

   -Y fixed_length_type_display_width

   -z new_password

   -Z new_password (and exit)


I set up my task to run every night at 2AM and it is working perfectly. The SQLCMD utility helped me resolve two challenges in the past month and I hope to find more opportunities to use SQLCMD to share moving forward.

Tags: SQL, Microsoft