SSMS SQLCMD Mode: a half done job?

I’ve always been aware of SQLCMD mode in SQL Server Management Studio but until a few days ago I never considered using it. So what is SQLCMD?

SQLCMD is a command line application that comes with Microsoft SQL Server, and exposes the management features of SQL Server. It allows SQL queries to be written and executed from the command prompt. It can also act as a scripting language to create and run a set of SQL statements as a script. Such scripts are stored as a .sql file, and are used either for management of databases or to create the database schema during the deployment of a database. source

Even better SQLCMD Mode allows us to mix T-SQL with these handy commands. You can activate the feature by clicking Query > SQLCMD Mode.

sqlcmd_mode_ssms

I remember reading on a blog somewhere that people starting out with T-SQL often try to do stuff like;

DECLARE @table VARCHAR(20);
SET @table = 'Customer';
 
SELECT *
FROM @table;

This doesn’t work but look what we can do in SQLCMD mode.

:setvar table Customer
 
SELECT *
FROM $(table);

Bang, we have a resultset. Now I was excited! There’s a few things in TSQL that will not accept a variable as input. Off the top of my head I can think of references to Linked Servers and OPENQUERY. Perhaps SQLCMD Mode was the answer to some of these, admittedly very rare, problems.

:setvar linkedservername MyLinkedServer
 
SELECT * 
FROM $(linkedservername).db.dbo.table;

Another resultset. So far, so good. Now lets try something a little different.

DECLARE @linkedserver CHAR(1);
SET @linkedserver = '1';
 
IF (@linkedserver = '1')
BEGIN
                PRINT '1';
                :setvar linkedservername MyFirstLinkedServer
END
ELSE
BEGIN
                PRINT '2';
                :setvar linkedservername MySecondLinkedServer
END
 
SELECT * 
FROM $(linkedservername).db.dbo.Customer;

This appears to work, when you reference genuine linked server, but if you change the names to ones that don’t exist you’ll get the following error.

Msg 7202, Level 11, State 2, Line 13
Could not find server 'MySecondLinkedServer' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Yep, it seems all the SQLCMD lines are executed regardless of the TSQL conditions around them. OK, so perhaps if I do all the assignment in TSQL and then feed a variable to the SQLCMD?

DECLARE @linkedservername VARCHAR(20);
SET @linkedservername = 'MyLinkedServer';
 
:setvar linkedservername @linkedservername;
 
SELECT * 
FROM $(linkedservername).db.dbo.Customer;

No joy with this either. So no flow control or TSQL variable assignments with SQLCMD mode? This is a big disappointment.  Not very many SQLCMD keywords are supported and no real flow control commands.

The Database Engine Query Editor supports the following SQLCMD script keywords:

[!!:]GO[count]

!!

:exit(statement)

:Quit

:r

:setvar

:connect server[\instance] [-l login_timeout] [-U user [-P password]]

:on error [ignore|exit]

:error |stderr|stdout

:out |stderr|stdout source

This happens because all the processing of SQLCMD happens client-side before the TSQL hits the database engine.The reason makes sense but I don’t like the result! Surely it wouldn’t have been that hard to add a few conditional commands to the product?

Don’t get me wrong, you can do some cool things with sqlcmd mode, but without conditional processing and variable handling it misses being something awesome by quite a long shot.


One Comment

Leave a Reply