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 .\Configuration1\Data.sql
  END
ELSE
  BEGIN
    print 'inserting generic data' 
:r .\GenericConfiguration\Data.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.

Byram

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="Scripts\Post-Deployment\Default.Script.PostDeployment.sql" DestinationFiles="Scripts\Post-Deployment\Script.PostDeployment.sql" OverwriteReadOnlyFiles="true" />
      <Copy Condition=" '$(Configuration)' == 'Debug' " SourceFiles="Scripts\Post-Deployment\Default.Script.PostDeployment.sql" DestinationFiles="Scripts\Post-Deployment\Script.PostDeployment.sql" OverwriteReadOnlyFiles="true" />
      <Copy Condition=" '$(Configuration)' == 'Configuration1' " SourceFiles="Scripts\Post-Deployment\Configuration1.Script.PostDeployment.sql" DestinationFiles="Scripts\Post-Deployment\Script.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.

Byram

With the GO statement requirement, I've worked around this before by executing the statement as dynamic SQL. That wasn't an MS Database Project though. YMMV. – Bhayani

Answer 2

I managed to work around the problem using the noexec method.

So, instead of this:

IF ('$(ConfigSetting)' = 'Configuration1')
 BEGIN
    print 'inserting specific configuration' 
    :r .\Configuration1\Data.sql
 END

I reversed the conditional and set NOEXEC ON to skip over the imported statement(s) thusly:

IF ('$(ConfigSetting)' <> 'Configuration1')
    SET NOEXEC ON

:r .\Configuration1\Data.sql

SET NOEXEC OFF

Make sure you turn it back off if you want to execute any subsequent statements.

Poinciana

Worked for me. Great idea. Still works in SSDT for VS 2013 in PostDeploy scripts. – Cayuse

This would be a fantastic solution...but it doesn't work for me. Looks like the NOEXEC is ignored and the :r statement is still executed, anyone has any idea why? – Caeoma

This worked perfectly for me. I just needed a way to only populate the tables on first publish. Simple and effective. Great work. – Disorganize

Answer 3

Here's how I am handling conditional deployment within the post deployment process to deploy test data for the Debug but not Release configuration.

First, in solution explorer, open the project properties folder, and right-click to add a new SqlCmd.variables file.

Name the file Debug.sqlcmdvars.

Within the file, add your custom variables, and then add a final variable called $(BuildConfiguration), and set the value to Debug.

Repeat the process to create a Release.sqlcmdvars, setting the $(BuildConfiguration) to Release.

Now, configure your configurations: Open up the project properties page to the Deploy tab. On the top dropdown, set the configuration to be Debug. On the bottom dropdown, (Sql command variables), set the file to Properties\Debug.sqlcmdvars.

Repeat for Release as: On the top dropdown, set the configuration to be Release. On the bottom dropdown, (Sql command variables), set the file to Properties\Release.sqlcmdvars.

Now, within your Script.PostDeployment.sql file, you can specify conditional logic such as:

IF 'Debug' = '$(BuildConfiguration)'
BEGIN
PRINT '***** Creating Test Data for Debug configuration *****';
:r .\TestData\TestData.sql
END

In solution explorer, right click on the top level solution and open Configuration Manager. You can specify which configuration is active for your build. You can also specify the configuration on the MSBUILD.EXE command line.

There you go- now your developer builds have test data, but not your release build!

Feathercut

Its 2015 and I have latest SSDT and VS2013 U4 and there is no tab called "Deploy" nor have I even seen any way of importing a file of SQLCMD variables. Really confused .. – Cooky

Answer 4

As Rob worked out, GO statements aren't allowed in the linked SQL scripts as this would nest it within the BEGIN/END statements.

However, I have a different solution to his - if possible, remove any GO statements from the referenced scripts, and put a single one after the END statement:

IF '$(DeployTestData)' = 'True'
BEGIN
    :r .\TestData\Data.sql
END
GO -- moved from Data.sql

Note that I've also created a new variable in my sqlcmdvars file called $(DeployTestData) which allows me to turn on/off test script deployment.

Scarecrow

I went down this path as well, however it imposes an implicit requirement that all scripts have uniquely-named variables. This can be a problem with complex databases. – Cayuse

I went down this path and it works just fine. Thanks! – Etherealize

Answer 5

I found a hack from an MSDN blog which worked fairly well. The trick is to write the commands to a temp script file and then execute that script instead. Basically the equivalent of dynamic SQL for SQLCMD.

-- Helper newline variable
:setvar CRLF "CHAR(13) + CHAR(10)"
GO
-- Redirect output to the TempScript.sql file
:OUT $(TEMP)\TempScript.sql

IF ('$(ConfigSetting)' = 'Configuration1')
  BEGIN
    PRINT 'print ''inserting specific configuration'';' + $(CRLF)   
    PRINT ':r .\Configuration1\Data.sql' + $(CRLF)
  END
ELSE
  BEGIN
    PRINT 'print ''inserting generic data'';' + $(CRLF) 
    PRINT ':r .\GenericConfiguration\Data.sql' + $(CRLF)
  END
GO
-- Change output to stdout
:OUT stdout

-- Now execute the generated script
:r $(TEMP)\TempScript.sql
GO

The TempScript.sql file will then contain either:

print 'inserting specific configuration';   
:r .\Configuration1\Data.sql

or

print 'inserting generic data';
:r .\GenericConfiguration\Data.sql

depending on the value of $(ConfigSetting) and there will be no problems with GO statements etc. when it is executed.

Dingdong

It's a shame it doesn't work. If pre/post deploy scripts supported :out it would be great but you'll get an "unsupported" error if you try. – Stovepipe

Answer 6

I was inspired by Rob Bird's solution. However, I am simply using the Build Events to replace the post deployment scripts based on the selected build configuration.

1. I have one empty "dummy" post deployment script.
2. I set up a pre-build event to replace this "dummy" file based on the selected build configuration (see attached picture).
3. I set up a post-build event to place the "dummy" file back after the build has finished (see attached picture). The reason is that I do not want to generate changes in the change control after the build.

Veliger