141

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?

Improve this question

4 Answers 4

Reset to default
284

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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This is also an issue when using Visual Studio's: Data > Schema Compare utility. If you run the resulting change script from within the utility you are fine, but if you decide to export/xcopy the change script after doing a comparison and then try to import/paste the resulting change script into SSMS then as above it will fail. Obviously this could also be an issue if you were including the db change script in a bundled deployment. So you need to turn on the SQL command mode before running the script either via the menu as above or ensure your custom install script does so before it runs.
In Visual Studio (2013): SQL menu->Execution Settings->SQLCMD mode
I'm running into this problem trying to use a scema compare generated script to build my database from my application on first startup after install, passing the script to an executenonquery(). I'll either need to find a way to execute this via SQLCMD mode or hack up the script replacing all the variables for a start.
Just an FYI - I used the create script generated from publishing my DB project, and then removed all references to the variables referenced using the setvar. I hardcoded my database name so instead of CREATE DATABASE [$DatabaseName] it was simply CREATE DATABASE MYDBNAME. This script now works to create my database via executenonquery() during my first application startup after install.
27

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

enter image description here

Improve this answer

Comments

2

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/

Improve this answer

1 Comment

Visual Studio will also report Syntax Errors against your project when a .sql file is open that uses :setvar. The Options change described here will eliminate the errors, and will avoid the "red squiggly underline" from the statement (you'll have to close and re-open any open .sql files to see the effect).
0

try replacing :setvar DatabaseName "MesProduction_Preloaded_KLM_MesSap"

with:

USE [MesProduction_Preloaded_KLM_MesSap]
GO
Improve this answer

1 Comment

Doesn't help evaluation of the variable in the rest of the script? $(DatabaseName).[dbo].[Whatever]

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.