SQL Server sqlcmd usage

I am in the process of putting my notes into web pages, please keep checking in later for more contents

quick link

mssql_02     home  
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)


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