Is it possible to automatically force SQLCMD mode in script?

Question

We're using Visual Studio Database Professional and it makes heavy use of SQLCMD variables to differentiate between environments while deploying.

I know there's several directives available for setting context (like :connect for server name). Is there a way within the script itself to force SQLCMD mode for execution? Part of our deployment process is to have DBA's examine and execute the scripts and it would be a nice safety net (so I don't have to remind them to set their execution mode to SQLCMD).

+1 Looking for a similar solution. Ever find out how to do it? — Brandon J. Boone
– Brandon J. Boone, Commented Nov 12, 2010 at 16:17

Community · Accepted Answer · 2017-04-13 12:42:39Z

4

Not a solution, but as a work-around, you could embed your script in some warning. This post inspired me to this code:

SET NOEXEC OFF; -- previous execution may have toggled it
:setvar IsSqlCmdEnabled "True"
GO
IF ('$(IsSqlCmdEnabled)' = '$' + '(IsSqlCmdEnabled)')
BEGIN
  PRINT('Use SqlCmd-mode!!');
  SET NOEXEC ON;
  -- RAISERROR ('This script must be run in SQLCMD mode.', 20, 1) WITH LOG
END
ELSE
BEGIN
  PRINT('Using SqlCmd-mode')
  -- insert the code you really want to execute:
  -- ...
END
SET NOEXEC OFF; -- do not disable next execution in this session

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Nov 15, 2011 at 9:45

Yahoo Serious

3,9782 gold badges36 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Edenfield Over a year ago

I know this is late but for anyone else who finds this in the future: the VS2010 publish scripts do basically this (they set a special __IsSqlCmdEnabled var then immediately check it's value) but they also SET NOEXEC ON as an added safety measure to prevent anything else in the script from running.

Yahoo Serious Over a year ago

@Micheal, thanks for the tip, I added the NOEXEC. (Untested, no SqlServer around at this moment, but it looks simple enough.)

Yahoo Serious Over a year ago

@Paul: that's because NOEXEC is still on for this session, and commands (inc. printing) will not be executed. If you end (and/or start) the command sequence with SET NOEXEC OFF it will repeat. To save people the trouble of having to type that (and think of that), I will also add that to the example.

Wayne Arthurton · Accepted Answer · 2011-10-06 18:44:13Z

0

This does not seem to be possible. I even checked the SSMS project mode.

However, if you create a database project in BIDS, the pre-deploy & post-deploy scripts run in SQLCMD mode by default.

I know that is not the answer you want, but it is the best I can give you w/o resorting creating a custom SSMS plugin that would do it for you based on some text in the script file.

answered Oct 6, 2011 at 18:44

Wayne Arthurton

6431 gold badge6 silver badges22 bronze badges

Collectives™ on Stack Overflow

Is it possible to automatically force SQLCMD mode in script?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related