5

Playing around with DB projects, Visual Studio generated a change script with the following lines:

GO
SET ANSI_NULLS, ANSI_PADDING --etc.;
SET NUMERIC_ROUNDABORT OFF;

GO
:setvar DatabaseName "Foo"
:setvar DefaultFilePrefix "Foo"
:setvar DefaultDataPath ""
:setvar DefaultLogPath ""

GO
:on error exit
GO

: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

GO
USE [$(DatabaseName)];

GO
CREATE NONCLUSTERED INDEX [SomeIndex]
    ON [dbo].[SomeTable]([SomeColumn] ASC);

GO

What is the significance of the colons at the beginning of the lines? This Stackoverflow question says that the colon is for bind variables, but then what does the colon in :on error exit do?

11

It's SQLCMD

What you have is a sqlcmd script.
The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt.

And sqlcmd has some sqlcmd specfic commands that start with a colon.

Some general info on sqlcmd below.

Screenshot of CLI with sqlcmd running

You can write SQL statements that are executed after you type GO. In the example, first the database AdventureWorks2012 is selected and after that a SELECT is executed.

-i to include scripts

However, you also have the possibility of executing a prepared script using the option -i, for example:

Script01.txt

enter image description here

-v to pass in a value

You can also create scripting varibales with the notation $(name) and passing a value from the command prompt using the option -v

SCript02.txt

enter image description here

Apart from the command prompt, code including scripting variables can be executed from SQL Server Management Studio, by selecting SQLCMD mode in a query window.

Icon for SQLCMD mode inside SSMS GUI

:SETVAR

In this environment the way you can set the values of the scripting variables is using :SETVAR command

enter image description here

:ON ERROR

The :ON ERROR [ EXIT | IGNORE] sets the action to be performed when an error occurs during script or batch execution.

Ignore

Screenshot for on error ignore

When the IGNOREoption is used, the error is ignored and the batch or script continues executing. However, the error message is printed.

Exit

Screenshot for on error exit

When the EXIT option is used, the execution is stopped and exists.

4

:On Error is an Execution Control Command in sqlcmd.

The post you referenced is referring to Oracle but the script you posted is for Sql Server's sqlcmd utility or sqlcmd mode

:On Error [ exit | ignore]

  • Sets the action to be performed when an error occurs during script or batch execution.
  • When the exit option is used, sqlcmd exits with the appropriate error value.
  • When the ignore option is used, sqlcmd ignores the error and continues executing the batch or script. By default, an error message will be printed.
1
  • According to the first link, All sqlcmd commands, except GO, must be prefixed by a colon (:), so that explains why the lines start with colons.
    – Aaroninus
    Dec 16 '14 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.