Variables to the rescue

Article
01/09/2007

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.

$(databasename)

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 NULL
AND DATABASEPROPERTYEX(N' $(databasename) ','Status') <> N'ONLINE')
BEGIN
RAISERROR(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 NOWAIT
RETURN
END
GO

:on error resume
CREATE 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:

You can provide a value through an inline :setvar <variablename> <value> statement in your T-SQL script.
Alternatively you can provide the name value pair via command line of the MSBuild task, which is what happens when you deploy from the IDE.
If there is no :setvar databasename statement inside the T-SQL script and the variable is not provided through the command line; we check if there is an environment variable with the name "databasename"; if it exists the value of the environment variable will be used to substitute
If none of the above conditions are true; the batch parser will abort and return with an error.

:setvar vs. command line

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)'

Now execute:

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.

Escaping variables

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.

Adding variables

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')
BEGIN
ALTER 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')
BEGIN
ALTER 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.

Making variables part of the dbproj file

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

This is how you can do this in X steps for an existing project that is opened in the Visual Studio IDE:

Right click on the project node (not the solution node)
Choose "Unload Project"
Right click on the unloaded project
Choose "Edit <filename>.dbproj"
Now the project file will be opened inside the XML editor, add the property group
Save and close the editor
Right click on the project node and choose "Reload project"
Done! Now you added two variables and made them part of the dbproj file

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)

-GertD