SQL SERVER Management Studio and SQLCMD Mode

Earlier today when I was helping my clients with Comprehensive Database Performance Health Check, they mentioned that they really enjoyed reading my recent series on SSMS, oStress, and sqlcmd. They really want to try it out sqlcmd option for the automation. However, their real challenge is the command prompt which makes coding and testing SQLCMD difficult. Honestly, there is an alternative way to quickly use SSMS SQLCMD mode as a development environment, let us learn about it today.

Read the following blog posts before getting started with this blog for a better understanding of the topic and also learning about how you can get started with SQLCMD.

• SQL SERVER – Performance Test – sqlcmd vs SSMS
• SQL SERVER – Performance Test – oStress vs SSMS
• SQL SERVER – Stress Testing with oStress – Load Testing

Now let us assume that you want to run the following query in the sample database WideWorldImporters.

 SELECT TOP 10 OrderID, OrderDate, ExpectedDeliveryDate
FROM WideWorldImporters.Sales.Orders

Now it is pretty straight forward to run the query in SSMS but if you want to run with SQMCMD in the command prompt you can do that with either just running this query as it is or using variables. Let us see how we can run the query with the help of a few variables in the command prompt.

First, connect to the command prompt.

sqlcmd -S "Quick\SQL19" -d SQLAuthority -E

Next, run the command with the variables.

:setvar Database WideWorldImporters
:setvar Table Sales.Orders
:setvar Cols "OrderID, OrderDate, ExpectedDeliveryDate"
USE $(Database)
SELECT TOP 10 $(Cols) FROM $(Table)
GO

Here is the image of how the command prompt will look with variables.

Now it could be very difficult to change anything in the command prompt if you have made any mistakes earlier. As you will have to re-type everything.

Thankfully SQL Server Management Studio (SSMS) has already built-in features to support the Command Prompt. You can enable that by going to Query Menu select selecting the option of SQLCMD Mode.

Once you enable that mode you can easily go to start using the SSMS query window as the SQLCMD prompt.

Whenever you specify any variables they will be automatically differently color-coded to give you better query writing experience. With the help of SSMS, now you can go and make any modification in various variables without typing out the entire thing again.

Reference: Pinal Dave (http://blog.SQLAuthority.com)