Scheduled SQL Server Data Export to CSV using SQLCMD

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.

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.