Overview of the SQLCMD utility in SQL Server August 13, 2018 by Prashanth Jayaram 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. Some important points to remember 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. 12345 :connect hqdbsp18PRINT '$(COMPUTERNAME)' GO:connect hqdbsp17PRINT '$(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. 12345678910111213141516171819202122232425 --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=2520079select * from [MES_REPL_Rpt_AP]..tb_F4801_woheader_MES where WorkOrderNumber=2520079GO --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=2520079select * 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. 12345678 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. 1234 USE [master]GOCREATE LOGIN [SQLShackTest] WITH PASSWORD=N'SQLShackDemo123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGO 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 12345 :connect hqdbsp18select * from sys.syslogins where name='SQLShackTest'GO:connect hqdbsp17select * 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 12345 C:\Users\ccov648>sqlcmd -S hqdbt01\SQL2017 -E1> use Python20172> :SETVAR tablename salesorderdetail2> 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 Getting started building applications using SQL Server DevOps Tools Overview of SQLCMD utility in SQL Server The BCP (Bulk Copy Program) command in action Continuous Database Delivery (CD) using SQL Server Tools SqlPackage.exe All about MSSQL-Scripter, the SQL Server cross-platform scripting Tool Getting started with Azure Data Studio (ADS); initial installation and configuration About Latest Posts Prashanth JayaramI’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) SQL Server In-Memory database internal memory structure monitoring - January 30, 2019 Using SQL Power Doc to Discover, Diagnose and Document SQL Server - January 21, 2019 Inventory and document your SQL Server estate using PowerShell - January 14, 2019 Related posts: All about MSSQL-Scripter, the SQL Server cross-platform scripting Tool Working with the SQL Server command line (sqlcmd) Discussing Backup and Restore Automation using SQLCMD and SQL Server agent An introduction to the bcp Utility (bulk copy program) in SQL Server How to use SQLCMD commands in the SSMS query editor