sqlcmd

download Microsoft Command Line Utilities 13.1 for SQL Server  use sqlcmd 

SQL Server sqlcmd usage

Hi, My name is Jimmy Sam, If you need DBA help, please email to jimmy_sam001@yahoo.com for further discussion. Thanks.

quick link

mssql_02     home  
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;"

top
top   prev   next  

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