database - Conditional logic in PostDeployment.sql script using SQLCMD

Question

I am using a SQL 2008 database project (in visual studio) to manage the schema and initial test data for my project. The atabase project uses a post deployment which includes a number of other scripts using SQLCMD's ":r " syntax.

I would like to be able to conditionally include certain files based on a SQLCMD variable. This will allow me to run the project several times with our nightly build to setup various version of the database with different configurations of the data (for a multi-tenant system).

I have tried the following:

IF ('$(ConfigSetting)' = 'Configuration1')
  BEGIN
    print 'inserting specific configuration' 
:r .Configuration1Data.sql
  END
ELSE
  BEGIN
    print 'inserting generic data' 
:r .GenericConfigurationData.sql
  END

But I get a compilation error: SQL01260: A fatal parser error occurred: Script.PostDeployment.sql

Has anyone seen this error or managed to configure their postdeployment script to be flexible in this way? Or am I going about this in the wrong way completely?

Thanks, Rob

P.S. I've also tried changing this around so that the path to the file is a variable, similar to this post. But this gives me an error saying that the path is incorrect.

Answer 1

UPDATE

I've now discovered that the if/else syntax above doesn't work for me because some of my linked scripts require a GO statement. Essentially the :r just imports the scripts inline, so this becomes invalid sytax.

If you need a GO statement in the linked scripts (as I do) then there isn't any easy way around this, I ended up creating several post deployment scripts and then changing my project to overwrite the main post depeployment script at build time depending on the build configuration. This is now doing what I need, but it seems like there should be an easier way!

For anyone needing the same thing - I found this post useful

So in my project I have the following post deployment files:

• Script.PostDeployment.sql (empty file which will be replaced)
• Default.Script.PostDeployment.sql (links to scripts needed for standard data config)
• Configuration1.Script.PostDeployment.sql (links to scripts needed for a specific data config)

I then added the following to the end of the project file (right click to unload and then right click edit):

  <Target Name="BeforeBuild">
      <Message Text="Copy files task running for configuration: $(Configuration)" Importance="high" />
      <Copy Condition=" '$(Configuration)' == 'Release' " SourceFiles="ScriptsPost-DeploymentDefault.Script.PostDeployment.sql" DestinationFiles="ScriptsPost-DeploymentScript.PostDeployment.sql" OverwriteReadOnlyFiles="true" />
      <Copy Condition=" '$(Configuration)' == 'Debug' " SourceFiles="ScriptsPost-DeploymentDefault.Script.PostDeployment.sql" DestinationFiles="ScriptsPost-DeploymentScript.PostDeployment.sql" OverwriteReadOnlyFiles="true" />
      <Copy Condition=" '$(Configuration)' == 'Configuration1' " SourceFiles="ScriptsPost-DeploymentConfiguration1.Script.PostDeployment.sql" DestinationFiles="ScriptsPost-DeploymentScript.PostDeployment.sql" OverwriteReadOnlyFiles="true" />
  </Target>

Finally, you will need to setup matching build configurations in the solution.

Also, for anyone trying other work arounds, I also tried the following without any luck:

1. Creating a post build event to copy the files instead of having to hack the project file XML. i couldn't get this to work because I couldn't form the correct path to the post deployment script file. This connect issue describes the problem

2. Using variables for the script path to pass to the :r command. But I came across several errors with this approach.