BTerrell Group Blog

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.