Browse 531 attributed reviews, viewable separately for our classroom and online training
What is SQLCMD mode, and how can you use it?
Part two of a three-part series of blogs

You can use SQLCMD mode to parameterise the connection, database name, table name and select columns for a query - and much more besides, as this blog explains.

  1. What is SQLCMD mode, and how can you use it?
  2. Invoking SQLCMD mode (this blog)
  3. Examples of the use of SQLCMD mode

Posted by Andy Brown on 24 November 2021

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Invoking SQLCMD mode

You can either turn the mode on for a single query, or for all new queries that you create.

Note that turning SQLCMD mode on has one big disadvantage: you lose SQL intellisense (you may be surprised how much you've been relying on this).  Oh, and the debugger also no longer works, although personally I don't find this useful anyway.

Turning SQLCMD mode in for a single query

So I've written the following query, but SQL Server Management Studio (SSMS) is having none of it:

Query not recognised

None of the syntax is recognised, because I haven't turned on SQLCMD mode for this query.

I can remedy this as follows:

Turning on SQLCMD mode

From the SSMS Query menu choose this option to turn on SQLCMD mode.

 

It's very hard to see from the icon next to it whether you've invoked this option or not.  The easiest thing to do is to type in a valid command beginning with a : and see whether it appears shaded in grey:

Grey line

Things are looking good.

 

Turning SQLCMD mode on for all new queries

To do this, go into SSMS options:

SSMS options

Choose this menu item from the Tools menu.

 

You can now tick this box:

Open in SQLCMD mode

Tick the box to do exactly what it says!

This blog has 0 threads Add post