This article is aimed at helping you understand the sqlcmd utility. Of course, this is a tool most of us have used it at several occasions given that the tool has been around for a decade now. However, to be a solid starting point, this article contains some examples that help you understand the tool from different viewpoints.

This article discusses the following:

1. How to enable SQLCMD
2. A few important points to remember
3. SQLCMD with scripting variables in SSMS
4. Passing variables (or argument) as T-SQL and or a SQL Script file
5. SQLCMD with scripting variables and Windows scripting in SSMS

What is SQLCMD?

sqlcmd is a command line utility that is part of the standard installation of SQL Server 2005 or higher, which enables interactive interface with any SQL Server instance to help perform the following tasks:

1. Execute Transact-SQL (T-SQL) statements in SSMS
2. Call a T-SQL script file
3. Use of environment variables in command mode
4. Store the output results of executed queries in a specified text file

Why SQLCMD?

sqlcmd is a simple, yet powerful scripting environment that helps with the automation of several tasks related to SQL Server. For example, you can write and execute a script that logs you into a specific instance of SQL Server, executes a script from a specified path, and redirects the output to a certain file.

Most of us SQL database administrators are already using PowerShell. The Invoke-SqlCmd cmdlet, from the sqlserver module that’s part of every SQL Server distribution since 2008, packs most of the capabilities of sqlcmd. Also, in SQL Server Management Studio, we have a SQLCMD mode, that simulates the capabilities of sqlcmd, and accepts some of the commands that are not part of the T-SQL language.

sqlcmd was an extension of the osql and isql command line utilities, that contains a rich set of parameters and options to enable automation of various administrative tasks.

1. It’s simple to bind SQL in a .bat file.
2. It runs interactively across various OS platforms.
3. It’s easy to pass command line arguments to a SQL file using sqlcmd.

How to enable SQLCMD

OK, so sqlcmd is a great tool; of that, we’re all convinced. Let’s now talk about how the query editor in SSMS can be used to write statements as sqlcmd scripts. The following example talks about the integration of Windows system commands and T-SQL statements into a single script.

It’s importatnt to remember that sqlcmd mode is not enabled by default. To enter into the sqlcmd mode in the SSMS query editor, follow the steps below:

1. In the Object Explorer right click the SQL Server and then click New Query
   OR
   Open a new Database Engine Query Editor window.
2. On the Query editor, click SQLCMD Mode.

To enable SQLCMD scripting mode:

1. On the SSMS, go to the Tools menu, select Options.
2. Expand the Query Execution tab, and select SQL Server, click the General page.
3. Check the box against By default open new queries in SQLCMD Mode.

1. SQLCMD commands must be prefixed with a colon. This makes the difference between SQLCMD commands and Transact-SQL clear.
2. The :CONNECT keyword is used to connect to a different server using the same session context.
3. The Operating system commands (in this case, if exists) and del commands used for the demo must start with two exclamation points (!!). The !! indicate that the commands are OS command, which will be executed using the cmd.exe command processor. In this example, the OS command, if exist $(file1) del $(file1) is passed as arguments to cmd.exe.
   
4. The variables that are used as part of the SQLCMD script are case-sensitive. For example, if we query the environment variables, we can see that COMPUTERNAME and computername are two different variables in the output. In the first, the environment variable is queried to return the name of the computer, where as in the second, SSMS isn’t able to resolve the variable and reports an error.

Examples of SQLCMD

The following example defines an integration of operating system commands and SQL statements together.

Variable are defined using SETVAR, connection is built using CONNECT the keyword, operating system commands are defined using !!, the output file is declared using the OUT keyword, and SQL Statements are placed to fetch the results based on the connection string.

SQLCMD with scripting variables, and Windows scripting in SSMS

In this example, login is created across multiple servers. T-SQL, sqlcmd and Windows scripting are all integrated, and run using the SSMS interface to create logins.

1. Enable XP_CMDSHELL
2. Define the T-SQL in a SQL file
3. Build the dynamic SQL
4. Verify the output

STEP 1: In this step, xp_cmdshell is enabled. It is not a recommended option, though. This has only been enabled for the purpose of the demo.

STEP 2: A SQL file, SysAdminLoginCreation.sql is created with the create login SQL statement.

The file looks like below

We create a for loop to loop through each SQL instances.

1. Iterate through the input file.
2. FOR Parameter %j defines the name of the SQL instance.
3. Call sqlcmd using the parameters as part of the command.
4. Repeat for each data item.

sqlcmd executed successfully!

Let’s check whether the logins are created on the those servers

The logins have also been created for the listed servers.

Passing variables (or argument) as T-SQL and SQL Script file

We can pass the variables as an input to the T-SQL or the SQL script file in the sqlcmd command console. The scripting variables are declared using :SETVAR keyword. The corresponding parameter values passed to T-SQL or SQL script file. The values are enclosed in $(variable name) are fed to the SQLs during the run time. Using -v (version) switch, the parameters are declared and fed to the script. We can list any number of variables with no delimiter in between the parameters. Lets follow the example to understand the mechanism better:

1. The salesorderdetails is the name of table assigned to tablename variable
2. The select statement is prepared along with the tablename variable

We can assume that database name will be provided as a SQLCMD variable during the deployment process; we can have exactly the same file deployed to all environments. The following example has two parameters, db and tablename. The input parameters are separated by a space in between.

Summary

SQLCMD mode in SSMS (SQL Server Management Studio) provides an interface to run SQL statements across multiple servers and across platforms. This is really helpful in many cases. In this article, we discussed the creation of logon across different SQL Server instance versions. Similarly, we performed data validation between the two subscribers. We also saw that, if there is a complex SQL query which requires multiple inputs, the values can be passed as parameters to the T-SQL or the SQL Script file.

Having said all that, we need to remember that SQLCMD mode only allows the use of sqlcmd environment commands within SSMS, however, without the typical IntelliSense support, or support for debugging. Maintenance of scripts that mix pure T-SQL and SQLCMD can require effort. Use it wisely!

Table of contents

• Author
• Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021