Wednesday, November 23, 2011

Running SQLCMD Database Deployment scripts from SQL Management Studio

This is a very common scenario for database projects created in Visual Studio:
  • Create a database project
  • Right click the project and select Properties
  • Set "Deploy action" to "Create a deployment script (.sql)
  • Right click the project and select Deploy
This will generate the .sql file which can be used for deploying the database separately.

However, if you try to run this file from SQL Management Studio, it will result in errors.

This is because the script contains some sqlcmdvars (:setvar) which are specific to SQLCMD. If you run the same script using sqlcmd.exe, it would work fine.
If you want to run it from SQL Management Studio, click Query->SQLCMD Mode and then execute the query.