Fixing Visual Studio's SQL schema compare database update script errors when trying to run the script in SQL Server Management Studio

Visual Studio 2010, 2012 and 2013 versions contain a useful feature called SQL Schema Compare in the Premium and Ultimate editions.

This feature allows you to compare two SQL databases (the source and the target), and notice their schema differences in tables, views, stored procedures and security. This is often a very useful feature, but if you are using this feature for the first time, there's a small problem you might run into.

When the two databases differ, there's a nice feature to generate a change script (Shift+Alt+G) to make the target database match the source database. But, if you take the resulting script and try to run it in SQL Server Management Studio (SSMS), you might receive errors like the following:

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near ':'.
Msg 911, Level 16, State 1, Line 39
Database '$(DatabaseName)' does not exist. Make sure that the name is entered correctly.
Msg 911, Level 16, State 1, Line 678
Database '$(DatabaseName)' does not exist. Make sure that the name is entered correctly.

This error happens because the change script generated by Visual Studio 2013 generates a script that is suitable for directly running by the SQLCMD command line utility. But even so, you can enable this mode in SQL Server Management Studio, and then be able to run the script without errors.

The solution: in SQL Server Management Studio, when you have the change script in from of you in a query window, go the the Query menu, and at the bottom, select the command "SQLCMD Mode".

In case you are interested, in the default, non-SQLCMD mode, SQL Server Management Studio does not understand the :setvar commands used at the beginning of the file. This is how a file might look like:

/*
Deployment script for MyDatabase

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

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

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "MyDatabase"
:setvar DefaultFilePrefix "MyDatabase"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"

GO

Hope this helps!