sqlcmd

SQL Server sqlcmd usage

quick link

rem promotion in batch
%1: server name
sqlcmd -t 900 -l 900 -x -I -S %1 -E -e -b -m-1 -d MyDB -i "My_SQL_01.sql" -o "%1_MyDB_My_SQL_01.sql_out.txt" 

-- alwayson cluster, read intent only
-- -d database name is needed, if use master database, always goes to primary
sqlcmd -SAG_Listener -E -dDatabaseName -Q "select @@servername;"
sqlcmd -SAG_Listener -E -dDatabaseName -Kreadonly -Q "select @@servername;"

sqlcmd usage

sqlcmd mode:
:setvar logintimeout 120
:setvar server mydbserver
:setvar user "sa"
:setvar pwd "sapass"
:connect $(server) -l ($logintimeout) -U $(user) -P $(pwd)

1 

sqlcmd interactive command

:connect myservername_instance
select @@servername;
go

:help
:connect my_servername
:list
:out filename.txt

1> :help
:!! []
  - 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 
  - Redirects error output to a file, stderr, or stdout.
:exit
  - Quits sqlcmd immediately.
:exit()
  - Execute statement cache; quit with no return value.
:exit()
  - Execute the specified query; returns numeric result.
go []
  - 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 |stderr|stdout
  - Redirects query output to a file, stderr, or stdout.
:perftrace |stderr|stdout
  - Redirects timing output to a file, stderr, or stdout.
:quit
  - Quits sqlcmd immediately.
:r 
  - 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  
  - Sets a sqlcmd scripting variable.

set sqlcmdeditor=notepad

sqlcmd -?

-b : on error exit
-l 1200 : increase login timeout time
-t 1200 : increase quert timeout time
-r0: redirects error-message output to the standard error-output device --- the monitor by default.
-r1 : direct error messages to stderr,all error messages and informational messages are redirected.
-x: disable variable substitution
-i input_file_name:
-o output_file_name:
-e : echo input

-h-1: no header
-W: remove trailing spaces

-K readonly: ApplicationIntent=ReadOnly keyword in a DSN file

set nocount on : don't display row count at the end of select
sqlcmd -h-1 -E -S MYSERVERNAME -Q "set nocount on select @@version"

sqlcmd -S MYSERVERNAME -E -e -b -Q "exec sp_databases"
sqlcmd -S MYSERVERNAME -E -e -b -d MYDBNAME -i Reboot_scripts.sql     -o Reboot_scripts_out.txt
sqlcmd -S MYSERVERNAME -E -e -b -d MYDBNAME -i CDS_BuffetList_v12.sql -o CDS_BuffetList_v12_out.txt

C:\>sqlcmd -?
usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f  | i:[,o:]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-K application intent]
  [-M multisubnet failover]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

sqlcmd -h -1 -W -E -S MYSERVERNAME -d MYDBNAME -Q "SET NOCOUNT ON ... ..."
-Q: execute query and exit
-q: execute query and stay
-i: script_file_name.sql 

-o: output file

-l 1200 -t 1200: logon timeout and execution timeout

-E: Windows authentication
-U login_id -P password: sql login

-e: echo input
-W: remove trailing space
-h -1: no header
-r0: message to stderr    -r0 2>outputfile.txt

-b : On Error, abort

set nocount on : suppress count feedback