Marko Zivkovic

How to use SQLCMD commands in the SSMS query editor

December 13, 2016 by

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

:setvar <var> <value>

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

:error < filename >

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:

See more

To boost your SQL Server development productivity, check out Free SQL Server Management Studio add-ins.

Useful links

 

Marko Zivkovic

Marko Zivkovic

Marko is a Mechanical engineer, who likes to play basketball, foosball (table-soccer) and listen to rock music. He is interested in SQL code, PHP development, HTML and CSS techniques.

Currently working for ApexSQL LLC as a Software Sales Engineer, he is helping customers with any technical issues and does quality assurance for ApexSQL Complete, ApexSQL Refactor and ApexSQL Search free add-ins.

View all posts by Marko Zivkovic
Marko Zivkovic
SQL Commands

About Marko Zivkovic

Marko is a Mechanical engineer, who likes to play basketball, foosball (table-soccer) and listen to rock music. He is interested in SQL code, PHP development, HTML and CSS techniques.

Currently working for ApexSQL LLC as a Software Sales Engineer, he is helping customers with any technical issues and does quality assurance for ApexSQL Complete, ApexSQL Refactor and ApexSQL Search free add-ins.

View all posts by Marko Zivkovic

3,401 Views