Previously we discussed how to use the Database Deployment feature in Visual Studio 2010. There are two options to deploy a database project. The first is to have Visual Studio deploy the scripts automatically. The second is to have Visual Studio build a deployment script that can be run via SQL Server Management Studio or SQLCMD. Running a Visual Studio generated deployment script in Management Studio is not as strait forward as it would seem.
When Visual Studio connects to a database and runs its deployment script, it does so via SQLCMD. This is seamless when a user wants to deploy his project directly through VS2010. When VS2010 creates a deployment script, it generates a script that contains SQLCMD Variables.
What are SQLCMD Variables
SQLCMD Variables are simply scripting variables that give a script more flexibility. The example in the Books Online is when a script is developed to be run agains multiple servers, scripting variables are used. Setting a scripting variable would allow the query to be run agains each server without the need to modify the query. Other example of SQLCMD variables and the full syntax are available in the Books Online article mentioned above
Running SSMS with SQLCMD Variables
The .sql script that is generated by VS2010 contains SQLCMD variables. Trying to run this via SSMS will generate “Incorrect syntax near ‘:’” errors. Queries that contain SQLCMD variables can be run through SSMS by turning on the ‘Run with SQLCMD’ option. This option can be found in the Query menu as ‘SQLCMD Mode’. This menu item is only visible if a query window is open and selected and allow queries that contain SQLCMD variables to be run in SSMS.
When the ‘Run with SQLCMD’ is enabled the scripting variables will be highlighted in grey by default. This color can be modified in the Fonts and Colors section of the options screen.
Pingback: Weekly Link Post 152 « Rhonda Tipton's WebLog