A quick demo on how schema migrations works in DbUp took an interesting turn as we tried to insert some values into our database:
1 2 3 4 5 6 |
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:
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.
Thank you for writing this up. Solved my issue 🙂