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:
- How to enable SQLCMD
- A few important points to remember
- SQLCMD with scripting variables in SSMS
- Passing variables (or argument) as T-SQL and or a SQL Script file
- 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:
- Execute Transact-SQL (T-SQL) statements in SSMS
- Call a T-SQL script file
- Use of environment variables in command mode
- 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.
- It’s simple to bind SQL in a .bat file.
- It runs interactively across various OS platforms.
- 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:
- In the Object Explorer right click the SQL Server and then click New Query
OR
Open a new Database Engine Query Editor window. - On the Query editor, click SQLCMD Mode.
To enable SQLCMD scripting mode:
- On the SSMS, go to the Tools menu, select Options.
- Expand the Query Execution tab, and select SQL Server, click the General page.
- Check the box against By default open new queries in SQLCMD Mode.
- SQLCMD commands must be prefixed with a colon. This makes the difference between SQLCMD commands and Transact-SQL clear.
- The :CONNECT keyword is used to connect to a different server using the same session context.
-
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.
- 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.
1 2 3 4 5 | :connect hqdbsp18 PRINT '$(COMPUTERNAME)' GO :connect hqdbsp17 PRINT '$(computername)' |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | --variable declartion :setvar subscriber1 HQMESRP01 :setvar subscriber2 HQMESRP02 :setvar file1 c:\Data1.txt :setvar file2 c:\Data2.txt --check for the file and delete if exists !!if exist $(file1) del $(file1) !!if exist $(file2) del $(file2) --connect to to the subscription 1 :Connect $(subscriber1) --redirect the SQL output to file :out $(file1) select * from [MES_REPL_Rpt_AP]..tb_F4801_woheader where wadoco=2520079 select * from [MES_REPL_Rpt_AP]..tb_F4801_woheader_MES where WorkOrderNumber=2520079 GO --connect to to the subscription 2 :Connect $(subscriber2) --redirect the SQL output to file :out $(file2) select * from [MES_HIST_AP]..tb_F4801_woheader where wadoco=2520079 select * from [MES_HIST_AP]..tb_F4801_woheader_MES where WorkOrderNumber=2520079 |
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.
- Enable XP_CMDSHELL
- Define the T-SQL in a SQL file
- Build the dynamic SQL
- 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.
1 2 3 4 5 6 7 8 | sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'xp_cmdShell', 1; GO RECONFIGURE; GO |
STEP 2: A SQL file, SysAdminLoginCreation.sql is created with the create login SQL statement.
1 2 3 4 | USE [master] GO CREATE LOGIN [SQLShackTest] WITH PASSWORD=N'SQLShackDemo123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO |
The file looks like below
We create a for loop to loop through each SQL instances.
- Iterate through the input file.
- FOR Parameter %j defines the name of the SQL instance.
- Call sqlcmd using the parameters as part of the command.
- Repeat for each data item.
1 | MASTER..XP_CMDSHELL 'for /f %j in (\\networkshare\IsDba\server.txt) do sqlcmd -S %j -i \\networkshare \IsDba\SysAdminLoginCreation.sql -E' |
sqlcmd executed successfully!
Let’s check whether the logins are created on the those servers
1 2 3 4 5 | :connect hqdbsp18 select * from sys.syslogins where name='SQLShackTest' GO :connect hqdbsp17 select * from sys.syslogins where name='SQLShackTest' |
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:
- The salesorderdetails is the name of table assigned to tablename variable
- The select statement is prepared along with the tablename variable
1 2 3 4 5 | C:\Users\ccov648>sqlcmd -S hqdbt01\SQL2017 -E 1> use Python2017 2> :SETVAR tablename salesorderdetail 2> select top(10) * from $(tablename); 3> go |
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.
1 | C:\Users\ccov648>sqlcmd -S hqdbt01\SQL2017 -E -i n:\IsDba\SalesOrderDetails.sql -v db=Python2017 tablename=salesorderdetail |
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
Prashanth Jayaram
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
