SQL Server ':setvar' Error

问题:

I am trying to create some script variables in T-SQL as follows:

    /*
    Deployment script for MesProduction_Preloaded_KLM_MesSap
    */

    GO
    SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

    SET NUMERIC_ROUNDABORT OFF;


    GO
    :setvar DatabaseName "MesProduction_Preloaded_KLM_MesSap"

However, when I run this, I get an error stating 'Incorrect syntax near ':'. What am I doing wrong?

回答1:

The :setvar only works in SQL command mode, so you are possibly within normal SQL execution in the management studio and have not swapped to command mode.

This can be done through the user interface in SQL Server Management Studio by going to the "Query" menu, and selecting "SQLCMD mode."

回答2:

Just enable sqlcmd mode in SQL Server Management Studio as described in following image.

回答3:

FOR SQL2012:

go to : tools/options/Query Execution and check the by default, open new queries in SQLCMD mode.

Hit the New Query button and make sure the variable definitions are highlighted, your script should run correctly now.

Previous versions:

http://blog.sqlauthority.com/2013/06/28/sql-server-how-to-set-variable-and-use-variable-in-sqlcmd-mode/

回答4:

try replacing :setvar DatabaseName "MesProduction_Preloaded_KLM_MesSap"

with:

USE [MesProduction_Preloaded_KLM_MesSap]
GO