SQLCMD Basics

Example Usage:

sqlcmd -S ComputerName\InstanceName -i MyScript.sql -o MyOutput.csv -t 1000 -W -s "," -X1

sqlcmd -S localhost\sqlsrv2012 -d AdventureWorks2012 -i C:\DataFiles\EmployeeQuery.sql -o C:\DataFiles\Employees.csv -t 1000 -W -s "," -X1


sqlcmd -S localhost\sqlsrv2012 -d AdventureWorks2012 -Q "SELECT FirstName, LastName FROM Person.Person WHERE PersonType = 'em' ORDER BY LastName, FirstName" -o C:\DataFiles\Employees.csv -t 1000 -W -s "," -X1

If you are in interactive mode specify “GO” to execute previous statements.

Syntax:

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-iinput_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-wcolumn_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) -? (usage)

 

Commands:

:!! [<command>] -Executes a command in the Windows command shell.
:connect server[\instance] [-l timeout] [-U user [-P password]] -Connects to a SQL Server instance.
:ed  -Edits the current or last executed statement cache.
:error <dest>-Redirects error output to a file, stderr, or stdout.
:exit -Quits sqlcmd immediately.
:exit() -Execute statement cache; quit with no return value.
:exit(<query>) -Execute the specified query; returns numeric result.
go [<n>]-Executes the statement cache (n times).
:help -Shows this list of commands.
:list -Prints the content of the statement cache.
:listvar -Lists the set sqlcmd scripting variables.
:on error [exit|ignore] -Action for batch or sqlcmd command errors.
:out <filename>|stderr|stdout -Redirects query output to a file, stderr, or stdout.
:perftrace <filename>|stderr|stdout -Redirects timing output to a file, stderr, or stdout.
:quit -Quits sqlcmd immediately.
:r <filename> -Append file contents to the statement cache.
:reset -Discards the statement cache.
:serverlist -Lists local and SQL Servers on the network.
:setvar {variable} -Removes a sqlcmd scripting variable.
:setvar <variable> <value> -Sets a sqlcmd scripting variable.

:exit(statement)
:Quit
:r <filename>
:setvar <var> <value>
:connect server[\instance] [-l login_timeout] [-U user [-P password]]
:on error [ignore|exit]
:error <filename>|stderr|stdout
:out <filename>|stderr|stdout

 

SQLCMD.exe File Location Options:

%ProgramFiles%Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn

%ProgramFiles%\Microsoft SQL Server\110\Tools\Binn

 

 

Resources:
https://technet.microsoft.com/en-us/library/ms162773.aspx

Using the SQLCMD Utility https://msdn.microsoft.com/en-us/library/ms180944.aspx
SQLCMD Basics https://www.simple-talk.com/sql/sql-tools/sql-server-sqlcmd-basics/
Advanced usage of SQLCMD https://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/

SQLCMD run multiple scripts https://www.mssqltips.com/sqlservertip/1543/using-sqlcmd-to-execute-multiple-sql-server-scripts/

SQL Server Command Line Utilities Download sqlcmd.exe and bcp.exe https://www.microsoft.com/en-us/download/details.aspx?id=36433

Connect to SQL Server When Administrators are Locked Out https://msdn.microsoft.com/en-us/library/dd207004.aspx

Leave a Reply

%d bloggers like this: