Do you have a need to make parts of your build conditional or environment dependent? Variables are here to help you.
The variable support inside Visual Studio Team Edition for Database Professionals is based on SQLCMD variables, see SQL Server Books Online for more details. When you are using SQLCMD variables inside your T-SQL scripts there are a couple of options and things you need to know about, which is exactly what this post will cover.
Today we rely on one specific variable, which represents the database name, expressed as the TargetDatabase property inside the dbproj file and represented inside your build script as $(databasename). This variable allows the system to deploy the project to any arbitrary database name. So you can deploy the same project and deploy it multiple times with a different database name.
The database variable show up in the build scripts. If you create a new database project; immediately call build and open up the build script you will find something like this:
:setvar databasename "VideoStore"
USE [master] GO
:on error exit
IF ( DB_ID(N'$(databasename)') IS NOT NULLAND DATABASEPROPERTYEX(N'$(databasename)','Status') <> N'ONLINE')BEGINRAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'$(databasename)') WITH NOWAITRETURNENDGO
:on error resumeCREATE DATABASE [$(databasename)] COLLATE SQL_Latin1_General_CP1_CS_AS GO
EXEC sp_dbcmptlevel N'$(databasename)', 90 GO
The value for the variable can be set in a couple of ways:
What happens when you have a :setvar inline and provide a value on the command line for the same variable name?
This is where we differ from SQLCMD.EXE; which always takes the last value supplied. To demonstrate this take the following example script (sqlcmd.sql):
:setvar COMPUTERNAME "MI"print '$(COMPUTERNAME)'
sqlcmd.exe -i sqlcmd.sql -v COMPUTERNAME=MO
The result is MI not MO
NOTE: Environment variables are only evaluated when the variable is not provided at all! So if there is no :setvar or no commandline definition of the variable, only then when the variable is referenced we evaluated to see if an environment variable with that name exists.
The deploy MSBuild task (SqlDeployTask) will override the :setvar value with the value provided at the command line through the SetVariablesXml property. This behavior allows you to specify a default value in the :setvar statement, or a value that guarantees the script to fail based on the behavior you want and override the value with the one from the build task.
Variables are only providing literal string replacement functionality; same as #DEFINE for those of you who are the pleasure of using these in other programming languages like C and C++. This means that variable are NOT smart and have NO notion of context, so you have to properly place them and escape them using square brackets or quotes when needed.
NOTE: When you use them inside an schema definition script (say a .table.sql or .index.sql script) you can only use variables inside square brackets or between (single) quotes (for object identifiers or literals) otherwise the T-SQL parser will not recognize them as correct T-SQL. When using variables inside pre- and post-deployment scripts you do not have this restriction.
So far we have been looking at how things work, now it is time to add some new variables and put them to work. One place where variables come in handy is in the post deployment file that defines files: storage.sql. Variables will allow use to make the location environment dependent.
Inside the storage file you will find something like this:
IF NOT EXISTS(SELECT 1 FROM dbo.sysfiles WHERE name = 'fgdb_data') BEGINALTER DATABASE [$(databasename)]ADD FILE(NAME = N'fgdb_data',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fgdb_data.ndf',MAXSIZE = 100MB,FILEGROWTH = 10MB)TO FILEGROUP [TABLES]END
We could parameterize this so the drive and directory get abstracted through a variable to:
:setvar drive "C:":setvar directory "Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA"
IF NOT EXISTS(SELECT 1 FROM dbo.sysfiles WHERE name = 'fgdb_data') BEGINALTER DATABASE [$(databasename)]ADD FILE(NAME = N'fgdb_data',FILENAME = N'$(drive)\$(directory)\fgdb_data.ndf',MAXSIZE = 100MB,FILEGROWTH = 10MB)TO FILEGROUP [TABLES]END
Now that we have parameterized the script, next we want to make the variables part of the project file, so we have them defined in a single place instead of scattered around in the code at various places through :setvar statements.
The database project understands the concept of variable, the only problem is that there is no UI support to add, edit and delete the variables inside the project file, so right now we will achieve this by performing surgery on the dbproj file :)
In order to enable variable support in the project you need to add a Property Group containing an XML block to the project file
<PropertyGroup><SetVariables><Variable Name="drive" Value="C:" /></SetVariables><SetVariables><Variable Name="directory" Value="Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA" /></SetVariables></PropertyGroup>
This is how you can do this in X steps for an existing project that is opened in the Visual Studio IDE:
Now that we made the variables part of the project file, we have gone full circle and you can build & deploy your project using the parameters you defined.
In the next blog on this subject we will look at how we can link the SQLCMD variable to MSBuild properties, how to make variables conditional and how to override variables from the command line when building.
I hope you found this usefull, to get you going here is a link to the sample project (UsingVariables.zip)