Today, I began evaluating using the new Visual Studio Team System Database Edition to manage and source control the database model used for one of our applications.
After importing an existing schema, I altered one of the columns (in Schema View) so as to create a deployment script which would then be run on the target SQL Server instance to modify it so as to match my changes in Schema View.
Instead of running the deployment script from within Visual Studio, I wished to run it from within SQL Server Management Studio (this is because I would like to hand off my DB script to the systems team to run the actual upgrade process).
So when I loaded up the script file into SQL Server Management Studio (I am using SQL Server 2005) and tried to run it I got this error : Incorrect syntax near ':'. And it was occurring on a setvar line.
:setvar DatabaseName "dbName"
The solution is super simple: You need to turn on SQLCMD mode before attempting to run the script (Query | SQLCMD Mode). Or hit the button on the SQL Editor toolbar.