Overview of the SQLCMD utility in SQL Server (2023)

This article aims to help you understand the sqlcmd utility. Of course, this is a tool that most of us have used on multiple occasions since the tool has been around for a decade. However, as a solid starting point, this article provides some examples to help you understand the tool from different angles.

This article covers:

1. How to enable SQLCMD
2. Some key points to remember
3. SQLCMD with Script Variables in SSMS
4. Passing variables (or arguments) as a T-SQL and/or as an SQL script file
5. SQLCMD with Windows Script and Script Variables in SSMS

What is SQLCMD?

sqlcmd is a command line utility that is part of the default installation of SQL Server 2005 or later and allows you to interactively interface with any instance of SQL Server to perform the following tasks:

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

Why SQLCMD?

sqlcmd is a simple yet powerful scripting environment that helps automate many SQL Server related tasks. For example, you can write and run a script that connects you to a specific instance of SQL Server, runs a script from a specified path, and redirects the output to a specific file.

Most of us SQL database administrators are already using PowerShell. The sqlserver module's Invoke-SqlCmd cmdlet, which has been part of every SQL Server distribution since 2008, contains most of the functionality of sqlcmd. Also, in SQL Server Management Studio, we have a SQLCMD mode that simulates the features 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, containing a variety of parameters and options to allow automation of various administrative tasks.

1. Binding SQL into a .bat file is easy.
2. It runs interactively on different operating system platforms.
3. It's easy to pass command line arguments to a sql file with sqlcmd.

How to enable SQLCMD

OK, so sqlcmd is a great tool; we are all convinced of it. Now let's talk about how to use the Query Editor in SSMS to write statements as sqlcmd scripts. The following example covers integrating Windows system commands and T-SQL statements into a single script.

It is important to remember that sqlcmd mode is not enabled by default. To switch to sqlcmd mode in SSMS Query Editor, follow these steps:

1. In Object Explorer, right-click SQL Server and click New Query
   OR
   Open a new Database Engine Query Editor window.
2. In the query editor, clickSQLCMD-ModusName.

To enable SQLCMD script mode:

1. Go to SSMSToolmenu, selectoptions.
2. expand thisquery executiontab and selectservidor SQL, tightenGenerallybook page.
3. check the boxBy default you open new queries in SQLCMD mode.

Some important points to remember

1. SQLCMD commands must be prefixed with a colon. This shows the difference between SQLCMD and Transact-SQL commands.
2. The :CONNECT keyword is used to connect to another server usingsame session context.
3. Operating system commands used for the demo (in this case, if any) and del commands must begin with two exclamation marks (!!). O !! indicate that the commands are operating system commands executed using the cmd.exe command processor. In this example, the operating system command, if present, $(file1) del $(file1) is passed as arguments to cmd.exe.
   
4. Variables used as part of the SQLCMD script are case sensitive. For example, if we query the environment variables, we will see that COMPUTERNAME and computername are two different variables in the output. In the first case, the environment variable is queried to return the computer name, while in the second case, SSMS is unable to resolve the variable and reports an error.

Examples of SQLCMD

The following example defines a common integration of operating system commands and SQL statements.

Variables are defined with SETVAR, connections are established with the CONNECT keyword, operating system commands are defined with !! defined, the output file is declared with the OUT keyword and SQL statements are placed to retrieve the results based on the connection string.

SQLCMD with Script Variables and Windows Scripts in SSMS

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

1. Ativar XP_CMDSHELL
2. Define T-SQL in a .sql file
3. Create dynamic SQL
4. check the output

STEP 1: In this step, xp_cmdshell will be activated. However, it is not a recommended option. This has been enabled for demo purposes only.

STEP 2: An SQL file, SysAdminLoginCreation.sql, is created using the create login SQL statement.

the file looks like this

We create a for loop to iterate through all SQL instances.

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

sqlcmd executed successfully!

Let's check if logins are created on these servers

Logins were also created for the listed servers.

Passing variables (or arguments) as T-SQL and SQL script file

We can pass the variables as input to T-SQL or SQL script file in sqlcmd command console. Script variables are declared with the :SETVAR keyword. Appropriate parameter values ​​are passed to the T-SQL or SQL script file. Values ​​enclosed in $(variable name) are fed to the SQLs at runtime. The -v (version) option is used to declare the parameters and pass them to the script. We can list any number of variables without separators between the parameters. Let's follow the example to better understand the mechanism:

1. Oorder detailsis the name of the assigned tabletable nameVariable
2. The select statement is prepared along with the tablename variable

We can assume that the database name will be provided as an SQLCMD variable during the deployment process. We can provide the exact same file in all environments. The following example has two parameters, db and tablename. Input parameters are separated by a space.

Summary

The SQLCMD mode in SQL Server Management Studio (SSMS) provides an interface for executing SQL statements on various servers and platforms. This is really helpful in many cases. In this article, we discussed creating logins on different SQL Server instance versions. Likewise, we perform data validation between the two subscribers. We've also seen that if you have a complex SQL query that requires multiple inputs, the values ​​can be passed as parameters to the T-SQL or SQL script file.

Also, we should bear in mind that SQLCMD mode only allows the use of commands from the sqlcmd environment from within SSMS, but without typical IntelliSense support or debugging support. Scripts that combine pure T-SQL and SQLCMD can be expensive to maintain. Use it wisely!

Index