If a user needs to identifed by username and password, these values can be passed using the -U and -P options:
C:\> sqlcmd -S . -U rene -P myBigSecret
Specify the database to connect to
The -d option controls which database sqlcmd connects to (so that there is no need to use the use statement).
C:\> sqlcmd -S … -d myDatabase
The value of the database can also be stored in the environment variable SQLCMDDBNAME.
Executing a script
sqlcmd executes the comma separated list of SQL script files if executed with -i:
C:\> sqlcmd … -i scriptOne.sql,scriptTwo.sql
When sqlcmd executes a script, it might print lots of (n rows affected) messages. In order to turn off such messages, the first statement in the script should be
The difference between -q and -Q is that -q does not terminate the sqlcmd session after executing the given statement, so it must be explicitly exited. -Q just executes the specified statement and automatically exits.
If the executed statement consists of multiple words, they must be quoted:
Alternatively, the :out statement can be used within an SQL script to specify a log file. The following script assumes that the environment variable TEMP is already set when sqlcmd is invoked:
:out $(temp)\log.txt
:setVar num 42
:setVar txt "Hello World"
print("The number is $(num), the text is $(txt)");
Alternatively, a variable can be set with when sqlcmd is started with the command line option -v varName=varValue.
The following script creates a table with one column and inserts one value into the table. All variables, table name, column name and inserted value, can be dynamically set when sqlcmd is executed with the -i and -y options:
--
-- Call this script with something like
-- sqlcmd -v tab=foo -v col=bar val=baz -i dynamic-table-and-column-name.sql
--
create table $(tab) (
$(col) varchar(10)
)
go
insert into $(tab) values ("$(col)")
go
With :connect, it's possible to connect to another instance
:connect Hostname\InstanceName
Raising the error level to suppress informational messages
sqlcmd operates with an error level which is a threshold value that controls which messages are sent to stdout. The higher this threshold value is, the less messages are displayed.
Because the default error level value is 0, even informational messages (such as Changed database context to … are emitted when an sql script executes a use DB statement is executed).
When sqlcmd is invoked, the error level can be set with the -m option.
sqlcmd -m 1 …
Note: the error level that is set with -m should not be confused with the error level that is returned to the caller with :exit(…).
Checking errorlevel in scripts
When a script terminates with an error, it sets the corresponding %errorlevel%.
This can be checked in a .bat file.
run-sql-script.bat
This batch script tries to run the script that is passed as argument.
If sqlcmd encounters an error, it immediately returns (because of the -b option) and sets %errorlevel%. This can be checked by the script
@set script_name=%1
@rem
@rem Invoke sqlcmd
@rem Pass it the -b flag so that it terminates
@rem when it encounters an error
@rem
@sqlcmd -b -i %script_name% -o run-sql-script.log
@rem
@rem after calling sqlcmd, we're checking errorlevel
@rem to find out if the script ran without error.
@rem
@if not errorlevel 1 goto script_ok
@echo "! Script %script_name% not ok, it returned %errorlevel% !"
:script_ok
sqlcmd statements are executed before the script content is run
It should be noted that sqlcmd statements are executed before the script is run as a batch. Particularly, variable assignments using :setVar might lead to unexpected results if used in an if statement:
if 3<5 begin
print("This text is printed");
:setVar txt "first assignment"
end else begin
print("This text is not printed");
:setVar txt "second assignment"
end
print("$(txt)");
Unable to complete login process due to delay in opening server connection.
I am not really sure when (or why) the error message Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Unable to complete login process due to delay in opening server connection. occurs, but I was able to connect to a SQL Server instance by increasing the login-timeout value with -l:
sqlcmd -S db.tq84 -l 300
sqlcmd mode in Management Studio
From a query editor in management studio, it's possible to go into sqlcmd mode: Menu Query -> SQLCMD Mode.
See also
:on error exit instructs sqlcmd to immediately abort a script if it encounters an error.