How to run a script from Command Prompt.

Problem: How to run a script from Command Prompt.?

Solution: sqlcmd Utility

Allows you to enter Transact-SQL statements, system procedures, and script files at the command prompt.

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job. This utility uses ODBC to execute Transact-SQL batches.

The following are the parameters you can specify at the syntax level of command prompt.

SQL command is case sensitive and it’s a best practice use integrated security so don’t use username and password unless required.

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]

   -g (enable column encryption)

   -G (use Azure Active Directory for authentication)

   -h rows_per_header 

   -H workstation_name 

   -i input_file 

   -I (enable quoted identifiers) 

   -j (Print raw error messages)

   -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, optional exit) 

   -y variable_length_type_display_width 

   -Y fixed_length_type_display_width 

   -z new_password  

   -Z new_password (and exit) 

   -? (usage) 

Example:

Example 1: In this example I would like to show how we can execute a sql command from command from command prompt.

Open command prompt and pass sqlcmd

Then pass the database

Then write the query and hit enter or go that gives the output.

OUTPUT:

Example 2: Execute a script on a particular location and save the output of error or print results and messages.

E – means trusted connection

S – means server you would like to run the script on

D- database to be used while executing

I – input file to sql command

O – write the output to a file in the case of an error or information.

  sqlcmd -E -S localhost -d DB -i test.sql -o test.out

 as you can see the above screen shots i have saved a sql script on to C:\temp folder and then executed going to the same directory from command using the above script: