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:
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 for the Server Name :SETVAR SourceServer DBS02
-- To connect to default Instance :CONNECT DBS02 -- To connect to a named instance :CONNECT DBS08\DEV
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]; GO SELECT [NAME] FROM SYS.DATABASES; GO