This evening, I was trying to build a script to configure database mirroring using SQLCMD. As I was not well-versed with sqlcmd, I started to search for a tutorial. A search on Bing, landed me on this page, which has a lot of information of how to use sqlcmd along with the available switches. We can access sqlcmd, either from command prompt or from SSMS.

Configuring SQLCMD Mode in SSMS

To start all the scripts in SQLCMD mode in SSMS, go to Tools –> Options –>Query Execution –>SQL Server. Select the checkbox at the bottom of the window as shown below:DefaultSQLCMD
If you select the above option, all the new query window can execute sqlcmd commands. The point to remember is Intellisense feature of SSMS will not work. Alternatively, if you don’t want to set the above option, then you can still execute a particular script using sqlcmd mode by selecting SQLCMD Mode from the Query menu as shown below:

Executing Script in SQLCMD Mode

Using sqlcmd mode, we can execute the t-sql queries along with the operating system commands from SSMS. For example, to list all the directories, we can execute the dir command from SSMS. The syntax to execute the command is:

-- To List all the directories
!! Dir
To define a variable, we use :SETVAR. The variable name should not contain any space in it. For example, to define a variable for the Server Name, use the following syntax:

-- To define a variable for the Server Name
:SETVAR SourceServer DBS02
To connect to a server with default SQL Server instance, use :CONNECTServerName. In case of a named instance, use :CONNECT ServerName\InstanceName.

-- To connect to default Instance
-- To connect to a named instance

The following script, connect to an instance using SQLCMD mode and list out all the databases available on the instance.

-- Define a variable for the ServerName
:SETVAR SourceServer DBS08

-- Connect to the SQL Instance and execute the code
:CONNECT $(SourceServer)
USE [master];
Time to built my Mirroring script, Hope this may help.

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in How To, Just Learned, SQL Server, SQLCMD and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s