Freigeben über

Variables to the rescue

  • Artikel

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:

  1. You can provide a value through an inline :setvar <variablename> <value> statement in your T-SQL script.
  2. 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.
  3. 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
  4. 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

<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:

  1. Right click on the project node (not the solution node)
  2. Choose "Unload Project"
  3. Right click on the unloaded project
  4. Choose "Edit <filename>.dbproj"
  5. Now the project file will be opened inside the XML editor, add the property group 
     
  6. Save and close the editor
  7. Right click on the project node and choose "Reload project"
  8. 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

Comments

  • Anonymous
    January 12, 2007
    The comment has been removed

  • Anonymous
    January 18, 2007
    Gert, I can't get this to work. Please could you check my forum posting at: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1127116&SiteID=1 Thanks Jamie Thomson

  • Anonymous
    January 18, 2007
    See http://SQLDev.Net/UsingVariables.zip for a sample project

  • Anonymous
    April 15, 2008
    Hi, I spend some time on this 5 posts : http://odetocode.com/Blogs/scott/archive/2008/02/03/11746.aspx from K Scott Allen, and I like particulary the feature of the SchemaChangesLog table which can help to compare two database version. I first developed a small console (Sql.smo, Tfs) which got 3 features: /enumchanges, /promotescript, /resetlocaldb Then I dig in vsDbPro to achieve the same goals. With the help of your current post (Variables to the rescue) I solved most of the problem. except one and may be you can help me to solve it: 1 - I included in the post script a call to a storeprocedure charged to add a row to the schemachangelog table 2 - I use the Variables with the dbproj to tell the SqlDeploy task which value assign to each store procedure parameter. 3 - I added a "Promote" task that use TF.exe to upload within the source control the builded script. this process rules well but the only problem is that the variables values are not included within the generated file, So replaying a script from the source control won’t produce the same result... So, is my explication enought clear ? ;o) Do you know a way to write formerly the variables value in the generated script? Regards, WoZoI

  • Anonymous
    April 19, 2008
    Hi gert, Finally I decided to solve the previously described problem by defining a simple msbuild task which looks like this:  <Target  Name="SetVarScript" DependsOnTargets="Build">      <SetVarScriptTask BuildScript="$(OutputPath)$(BuildScriptName)" FgSetVariablesXml="$(SetVariables)" VariablesToProceed="MajorDbVersion; MinorDbVersion"></SetVarScriptTask>  </Target> for each variable name enumerated in the VariableToProceed attribute it adds a : setvar VariableName "VariableValue" to the output file produced by the Build task. Regards

  • Anonymous
    April 22, 2008
    It is common to use SQL Scripts to create or initialize a DB from a setup program. For example TS DB...

  • Anonymous
    February 11, 2010
    How can I set and read a variable that tells me if an incremental deploy is occurring or a create database is occurring.  I have code that populates specific config tables that needs to run if a database create is occurring.  However, should it be an incremental this obviously does not need to run.

  • Anonymous
    September 06, 2010
    Why on earth did you use the yellow font datadude?  Why?!?

  • Anonymous
    January 29, 2013
    i can't read the yellow font