Check for SQLCMD mode and exit otherwise

Question

Check for SQLCMD mode and exit otherwise

Stephen Hulbert 41

We have a problem with DEV's running SQLCMD scripts in SSMS without SQLCMD mode turned on.

Code like this (but not this, obvs):

use PRODUCTION  
  
:`setvar CurrentDatabase "DEVELOPMENT"  
  
use [$(CurrentDatabase)]  
truncate table Business.CriticalData

What I'd like is for a check to be made against SQLCMD mode, and the script terminated if it's not running.
Reading around, it seems no implementation of the following metacode is supported:

if @SQLCMDMODE = 0   
exit

All of the solutions seem to rely on the user being an admin and don't work if they're not.

E.g. "RAISERROR 20" and "set noexec on"

This is such a fundamental requirement.

Is there really no way to do it?

Accepted answer

3 additional answers

Your answer

Answer 1

Tom Phillips 17,771

Because this is an SSMS option, not a TSQL option, you cannot directly test for it.

Please see:
https://jonlabelle.com/snippets/view/sql/check-if-sql-command-mode-is-enabled

Answer 2

Stephen Hulbert 41

That does not work if you're not an admin.

Or, it just doesn't work at all.

This is so simple and necessary, there must be something in a bigger picture.

Tom Phillips 17,771 Reputation points

2022-03-04T16:44:22.787+00:00
It works fine if you are not a sysadmin. What exactly is the error you are receiving?

:setvar IsSqlCmdEnabled "True" go if N'$(IsSqlCmdEnabled)' not like N'True' begin print N'SQLCMD mode must be enabled to successfully execute this script.'; print N'Disabling script execution.'; set noexec on; end; go :setvar CurrentDatabase "DEVELOPMENT" use [$(CurrentDatabase)] PRINT 'Changed Database' go set noexec off;

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ':'.
SQLCMD mode must be enabled to successfully execute this script.
Disabling script execution.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ':'.
Msg 911, Level 16, State 1, Line 13
Database '$(CurrentDatabase)' does not exist. Make sure that the name is entered correctly.
Stephen Hulbert 41 Reputation points

2022-03-04T18:47:49.18+00:00

right.

so the parser still throws errors about colons and curly brackets.

but the SQL commands are not executed.

it actually turns SQL commands off altogether.

thanks for that.

still bemused as to why there isn't a native SSMS directive, though.

oh well.
Tom Phillips 17,771 Reputation points

2022-03-04T19:27:01.977+00:00

You cannot stop the parser from running, and the GO statements complicate anything you try to do. So that is the best solution.

Answer 3

Stephen Hulbert 41

Further, since the whole script is pre-parsed by SSMS, why does it allow the script to run AT ALL if it's got SQLCMD syntax in it?

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-03-04T22:46:50.12+00:00

Because when you don't have SQLCMD mode on, SSMS only parses out GO alone on a line and sends everything in between to SQL Server.

SSMS does not really understand the SQL statements - that would be bad, because then it would not be able to work with future versions of SQL Server.

As a point of interest: I never use SQLCMD mode in SSMS.

Answer 4

Christopher 0

SSDT does this via this bit for publish scripts:

:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END

Share via

Check for SQLCMD mode and exit otherwise

3 additional answers

Your answer