Lines starting with colons in SQL Server

Question

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?

Answer 1

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.

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:

-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

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.

:SETVAR

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

:ON ERROR

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

Ignore

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

Exit

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

Answer 2

: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.