Use SQLCMD Utility In SQL Server

The sqlcmd utility is a command line tool in SQL Server that let us submit the T-SQl statements and batches. SQLCMD generally use for repetitive tasks like unit testing and batch processing. The sqlcmd utility in SQL Server is a handy tool for running ad hoc queries or creating script files to perform routine tasks or automate procedures. To use sqlcmd interactively, or to build script files to be run using sqlcmd, users must understand Transact-SQL. Users interactively enter Transact-SQL statements similar as working at the command prompt.

How to activate an sqlcmd utility

There are two ways to activate the sqlcmd utility.

Method 1-

Go to the following directory “C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn” and run the SQLCMD.EXE.




Method 2-

Got start and open the command prompt, now write SQLCMD and press the enter.



Options in SQLCMD-

The following options are used in SQLCMD utility.

Server Option(-S) - Define the instance of SQL Server to which SQLCMD will connect.

Syntax - SQLCMD –S <Server_Name>



Authentication Option(-E,-U,-P) - Define the authentication option. Default value is –E.

Syntax - SQLCMD –U <Authentication_Option>

Input Option(-Q,-q,-i) - Define the location of input.

Syntax - SQLCMD –I <File_Name>

Output option(-o) - Define the location of file in which SQLCMD put its output.

Syntax - SQLCMD –o <File_Name>

At the sqlcmd prompt, we can type both Transact-SQL statements and sqlcmd commands, such as GO and EXIT and each Transact-SQL statement is put in a buffer called the statement cache. In SQLCMD each command is send to SQL Server instance after the GO command and press the enter.



In sqlcmd new statement is assumed after the semi colon(;). If we did not mention the semi-colon, then the multiple line are assumed as single statement.



Quoted String

String in sqlcmd can easily manipulated, but characters with quotations marks can be inserted into a string by entering two consecutive quotation marks.

Example



Select a database

Similar to SQL Server query editor, in sqlcmd we can write “USE Database_Name” to select the database as below.



The “Changed database context to Demo” line indicating that current selected database is “demo”.

Run SQL query

Write the SQL query that you want to execute and after completion of query write the “GO” word and press enter.



Run SQL Script

Now we create a SQL script in C:\TC directory and paste the following code in MYSQL.SQL file and try to run the script.

USE Demo
GO
SELECT TOP (4) EmployeeName From Employee;
GO

Now we run this script.



Write the output of sqlcmd in a file

To write the output of sqlcmd, we use the –o option with the location of file in which we want to insert the result.

Example



In above query we read the script from “C:\TC\MYSQL.SQL” file and insert the result in file that is placed at “C:\TC\MYSQl.txt”.



SQLCMD with scripting variables

Variables that are used in scripts are called scripting variables. Scripting variables can be defined explicitly by using the setvar command, or implicitly by using the sqlcmd -v option. We can use the environment variables at command prompt using the SET. Scripting variables enable one script to be used in multiple scenarios. SET command is used before the staring sqlcmd. We use “$” symbol to use a variable in SQL query.



Execute the stored procedure-

Using the sqlcmd, we can execute the stored procedure. The following example will show the demo and how to execute the stored procedure using sqlcmd.

We have the following stored procedure to execute.

USE [project]
GO
/****** Object: StoredProcedure [dbo].[USP_Retrieve_Tehsil] Script Date: 5/10/2016 8:22:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_Retrieve_Tehsil](@City_Id INT)
AS
BEGIN
SELECT tmti.Tehsil_Id,tmti.Tehsil_Name,tmti.Latitude,tmti.Longitude FROM dbo.TblMaster_Tehsil_Info tmti
WHERE tmti.City_Id=@City_Id
ORDER BY tmti.Tehsil_Name
End

Now we execute above stored procedure using the sqlcmd.



If we execute the above stored procedure in SQL SERVER Management Studio then same result will be obtained.



Sqlcmd generally used for repetitive tasks like unit testing and batch processing. Using Sqlcmd we can run a query on multiple servers simultaneously. Generally, Sqlcmd is used to run the ad hoc Transaction SQL and scripts.