Skip to content

DbUp: Variable X Has No Value Defined

A quick demo on how schema migrations works in DbUp took an interesting turn as we tried to insert some values into our database:

Beginning database upgrade
Fetching list of already executed scripts.
Executing SQL Server script 'DBMigration.Scripts.Script0004.sql'
Upgrade failed due to an unexpected exception:
System.InvalidOperationException: Variable a4 has no value defined
   at DbUp.Engine.Preprocessors.VariableSubstitutionPreprocessor.ReplaceToken(Match match, IDictionary`2 variables, Match commentMatch) in C:\projects\dbup\src\DbUp\Engine\Preprocessors\VariableSubstitutionPreprocessor.cs:line 62

Until now I never had such a problem and we were a bit puzzled. Where is the variable a4 coming from? All we wanted to do is to run this insert command:

1
2
3
4
5
INSERT INTO [dbo].[UnicodeExample]
           ([German])
     VALUES
           ('$a4$.......')
GO

Executing the same script in SQL Server Management Studio worked without a problem, so DbUp may be the source of the problem. A few searches for different parts of the exception brought us to the chapter on variable substitution in the documentation of DbUp.

Our value had unfortunately the form of a variable that DbUp tried to substitute. We don’t want to substitute variables and could therefore disable this feature with one additional line in the setup code:

1
2
3
4
5
6
7
8
var upgrader =
    DeployChanges.To
        .SqlDatabase(connectionString)
        .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
        .WithTransactionPerScript()
        .WithVariablesDisabled()
        .LogToConsole()
        .Build();

Running DbUp once more inserted our data without a problem:

DbUp now could run our script

Conclusion

Some features of a library can be found only by accident. Luckily for us the substitution of variables is one that can be turned off without much work. I hope you can use this post to fix our problem when DbUp has a problem with your data.