SQLCMD Mode allows creating, testing, executing SQLCMD commands or scripts in SQL Server Management Studio directly in the query editor. This option is available since SQL Server 2005.
This article will explain some of the SQLCMD script keywords that the Database Engine Query Editor supports.
To write or edit SQLCMD scripts in the query editor, the SQLCMD mode needs to be enabled. By default, this mode is turned off.
To enable SQLCMD mode, click the SQLCMD Mode option under the Query menu:
Another way to enable the SQLCMD Mode is by using a combination of keys ALT+Q+M from the keyboard.
In SSMS, there is an option to set the query windows to be opened in the SQLCMD mode by default. To do that, go to the SSMS main menu and under the Tools menu choose the Options command:
This will open the Options window. From the list, choose the Query Execution -> SQL Server -> General and check the “By default, open new queries in SQLCMD mode” checkbox:
When using SQLCMD mode the IntelliSense and Transact-SQL debugger are turned off in the Database Engine Query Editor.
In the SQLCMD mode, two types of statement can be entered: the first are the SQLCMD and second are T-SQL statements.
In the example below, some of the SQLCMD script keywords will be explained:
:CONNECT ZIVKO\ZIVKO2014 :OUT C:\Users\Marko\Data.txt
USE AdventureWorks2014; SELECT a.City, a.PostalCode FROM Person.Address a
When executing the code, the result in the query editor will be:
The SQLCMD commands are automatically highlighted in gray and the T-SQL statements appear normal as it appears in the regular query.
Most of the SQLCMD commands begin with a colon (:). For a few SQLCMD commands, such as QUIT and EXIT though, a colon (:) can be omitted.
For example, the quit command will work the same as the command : quit.
This is enabled because of backward compatibility with the osql utility.
Only one SQLCMD command can be in each line. If two or more SQLCMD commands appear in one line:
:connect ZIVKO\ZIVKO2014 :out C:\Users\Marko\Data.txt
the following error will appear:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing :setvar.
:connect SQLCMD script keyword
This creates a connection to a SQL Server instance. If the instance is the default one, or if is specified by the server/instance name, then SQLCMD uses Windows authentication for connecting to SQL Server with a current account:
:connect (local) or
:connect ZIVKO\ZIVKO2014
SQLCMD also allows specifying a username and password when connecting to an instance. To include a username, add -U switch and then the name of a user. To include a password, use -P switch and enter a password:
:connect ZIVKO\ZIVKO2014 -U <username> -P <password>
:out SQLCMD script keyword
This command provides a location where the query results will be redirected. In this example, the results will be redirected to the Data.txt file:
:out C:\Users\Marko\Data.txt
To change the appearance of the results in the Data.txt file, go to Tools -> Options -> Query Results -> SQL Server -> Results to Text and from the Output format combo box, choose for example the Comma delimited option:
After executing the same T-SQL statement, the result will be:
SQLCMD is very useful when it needs to execute the same code on multiple databases or servers. In the example below, it is shown how a user can be added on multiple databases.
Open a new query editor, switch to the SQLCMD mode (QuerySQLCMD Mode) and paste the following code:
:setvar username “Marko” :setvar login “Zivko”
EXEC sp_grantdbaccess ‘$(login)’, ‘$(username)’ GO
Save this code as the User.sql file on this location “C:\User”.
sp_grantdbaccess –Stored procedure that adds a user to the current database.
Setvar script keyword
Example
This defines sqlcmd variables. The first item is the name of the sqlcmd variable (<var>) and the second item is the value of the sqlcmd variable (<value>). Variable names (<var>) are case insensitive.
Variable name cannot have blank spaces. The following sqlcmd variable name:
:setvar user name “Marko”
will raise this error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing :setvar.
Variable identifier
Example
$(<var>)
The variable identifier can be used as a database name, table names, column names, values in queries etc:
:setvar Table Person.Person :setvar Database AdventureWorks2014 :setvar Value “FirstName +’ ‘+ LastName AS Name”
USE $(Database) SELECT $(Value) FROM $(Table)
The result will be:
If the sqlcmd value contains blank spaces, the values must be enclosed in quotation marks:
:setvar Value “FirstName +’ ‘+ LastName AS Name”
Otherwise the following error will appear:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing :setvar.
In a new query window, paste the following code:
:setvar SQLFile “User.sql” :setvar Error “Errors.txt” :setvar Path “C:\User\” – -specify the path of the error file :error $(Path)$(Error)
USE AdventureWorks2014 – – Set the database name :r $(Path)$(SQLFile) USE Test – – Set the database name :r $(Path)$(SQLFile) USE Test 1 – – Set the database name :r $(Path)$(SQLFile) USE [Adventure – Works] – – Set the database name :r $(Path)$(SQLFile)
Error script keyword
Example
Redirect all errors that occur during execution to the specified file name, in this case, this will be the Errors.txt file on this location C:\User\”.
< filename >
This file is automatically created and records all errors that appear during the execution of the code:
If this file already exists, the content from the previous session will be truncated.
:r < filename > script keyword
This sqlcmd command reads input from <filename>, in this example, from the User.sql file and loads it into the statement cache.
Other SQLCMD commands
On error script keyword
Example
:on error [exit | ignore] script keyword
With this sqlcmd command, an action can be set that will be performed when an error occurred.
:on error exit
When the exit option is set the sqlcmd exits with an error message. If the user already exists in the database and the code below is executed:
:setvar SQLFile “User.sql” :setvar Error “Errors.txt” :setvar Path “C:\User\” – -specify the path of the error file :error $(Path)$(Error) :setvar Report “Report.txt”
:out $(Path)$(Report) :on error exit
USE AdventureWorks2014 – – Set the database name :r $(Path)$(SQLFile) USE Test – – Set the database name :r $(Path)$(SQLFile) USE Test 1 – – Set the database name :r $(Path)$(SQLFile) USE [Adventure – Works] – – Set the database name :r $(Path)$(SQLFile)
the error message will be:
:on error ignore
When the ignore option is set, sqlcmd ignores the error and continues to execute the script.
:reset script keyword
Throw away the statement cache
:quit script keyword
Stops sqlcmd immediately
:exit script keyword
Exits sqlcmd immediately and returns no value
:exit() script keyword
Executes the batch, then exit and returns no value.
:exit(query) script keyword
Executes a batch that includes the query, returns the results of the query and then quits:
:setvar SQLFile “User.sql” :setvar Error “Errors.txt” :setvar Path “C:\User\” – -specify the path of the error file :error $(Path)$(Error) :setvar Report “Report.txt”
:out $(Path)$(Report) :exit(Select * from Person.AddressType)
USE AdventureWorks2014 – – Set the database name :r $(Path)$(SQLFile) USE Test – – Set the database name :r $(Path)$(SQLFile) USE Test 1 – – Set the database name :r $(Path)$(SQLFile) USE [Adventure – Works] – – Set the database name :r $(Path)$(SQLFile)
The results in the Report.txt will be:
He is a prolific author of authoritative content related to SQL Server including a number of “platinum” articles (top 1% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/export, LocalDB, SSMS, and more.
In his part-time, Zivko likes basketball, foosball (table-soccer), and rock music.
See more about Marko at LinkedIn
View all posts by Marko Zivkovic
- How to connect to a remote MySQL server using SSL on Ubuntu - April 28, 2020
- How to install MySQL on Ubuntu - March 10, 2020
- Using SSH keys to connect to a remote MySQL Server - November 28, 2019