Limit a sql script to only run with SQLCMD turned on
I really like SQLCMDso much so that most of my
deployment scripts use SQLCMD. Its got some great features such as
:on error exit. Forcing your script to exit when an error
occurs.
or
:r <filename> to read the contents of a file
There is one downside and that is if you run a sqlcmd script in
management studio with out turning on sqlcmd then all the sqlcmd stuff is
ignored. Ok so you get an error, but good old management studio doesn't stop on
errors. If you have a script that is seperated by GOs then the script will
continue even if an error occurs. This can cause all sorts of problems if you
haven't coded you script correctly.
So I started to think how to get round this and came up with
this snippet
:setvar SqlCmdIsOn Yes
go
if ('$(SqlCmdIsOn)'<>'Yes')
RAISERROR ('Cannot run without
being in SQLCMD mode',20,1) with log
go
print'hello'
Simply put, the first
statement will fail, which means the sqlcmd variable won't be set to Yes
resulting in the if clause evaluating as true. This results in the error being
raised. Because this is a level 20 error the connection will be closed causing
management studio to not continue with the
script.
-