HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Can I enable SQLCMD mode from inside a script?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
scriptcanenablemodesqlcmdfrominside

Problem

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

Solution

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)' = '

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. + '(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.

Code Snippets

: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

Context

StackExchange Database Administrators Q#5468, answer score: 20

Revisions (0)

No revisions yet.