19

Is there something like a directive which I can use in a script to force SSMS to enable/disable SQLCMD mode?

Improve this question

2 Answers 2

Reset to default
20

As has been said, there isn't a way.

However, one thing we do is to include a quick check in our script header, to determine whether SQLCMD mode is on (and terminate the script if not):

:setvar DatabaseName "MyDatabase"
GO
IF ('$(DatabaseName)' = '$' + '(DatabaseName)')
    RAISERROR ('This script must be run in SQLCMD mode. Disconnecting.', 20, 1) WITH LOG
GO
-- The below is only run if SQLCMD is on, or the user lacks permission to raise fatal errors
IF @@ERROR != 0
    SET NOEXEC ON
GO

PRINT 'You will only see this when SQLCMD mode is on'
-- Rest of script goes here

GO
SET NOEXEC OFF
GO

There is a reason Severity 20 is used, it tends to kill the connection immediately, preventing any more script code from running, even if there are GO batch terminators later in the script.

Severities 20-24:

Indicate system problems and are fatal errors, which means that the Database Engine task that is executing a statement or batch is no longer running. The task records information about what occurred and then terminates. In most cases, the application connection to the instance of the Database Engine may also terminate. If this happens, depending on the problem, the application might not be able to reconnect.

Error messages in this range can affect all of the processes accessing data in the same database and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 24 are written to the error log.

Improve this answer
10
  • 1
    For ssms the first 2 lines would be enough and for sql 2000 Query Analyzer it works, but the message is not displayed. Commented Sep 8, 2011 at 6:07
  • But wouldn't the script just continue executing from the next GO? Commented Sep 8, 2011 at 23:34
  • 5
    As mentioned here, you can also set SET NOEXEC ON as an added safety measure to prevent anything else in the script from running. Commented Apr 16, 2013 at 10:25
  • 1
    @Dan Nolan: I see, somehow I missed the point of 3rd line. So if I understand it correctly, there's no solution to cover SQLCMD mode off. It will essentially display incorrect syntax near ':' because of :setvar at the beginning... Commented Jun 28, 2019 at 7:19
  • 1
    @youcantryreachingme RAISERROR severity level 20 (and above) is considered by SQL as a fatal error, which results in the connection being dropped immediately, hence no further batches (not even the SET NOEXEC ON) is executed. The reason why the NOEXEC statements are there is to cover the situation where the executing user does not have sysadmin/ALTER TRACE privileges, which is needed for raising fatal errors. Commented Aug 8, 2019 at 7:25
8

No.

But you can always run in SQLCMD mode and have T-SQL in it though

To make a clear distinction between SQLCMD commands and Transact-SQL, all SQLCMD commands, need to be prefixed with a colon (:).

Improve this answer
2
  • Is this still valid for SQL Server 2012 / 2014 / 2016? Commented Jun 14, 2018 at 14:43
  • 1
    @hot2use: Yes, I've used it on 2014/2016 Commented Jun 17, 2018 at 20:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.