Conditional logic in PostDeployment.sql script using SQLCMD

Question

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

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

I 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 customize their postdeployment script to be flexible this way? Or do I completely disagree about this?

Thanks Rob

PS I also tried to change this so that the file path was a variable similar to this post . But that gives me an error saying that the path is wrong.

Answer 1

UPDATE

Now I found that the if / else syntax above does not work for me, because some of my related scripts require a GO statement. Essentially: r just imports inline scripts, so this becomes invalid sytax.

If you need a GO statement in related scripts (like me), then there is no easy way around this, I finished creating some post-deployment scripts and then modified my project to overwrite the main post-failure script during build depending on assembly configurations. This now does what I need, but there seems to be an easier way!

For those who need the same thing - I found this post helpful

So, in my project, I have the following files for deployment after publication:

• Script.PostDeployment.sql (empty file to be replaced)
• Default.Script.PostDeployment.sql (links to scripts needed to configure standard data)
• Configuration1.Script.PostDeployment.sql (script links required for a specific data configuration)

Then I added the following to the end of the project file (right click to upload, and then right click):

  <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 configure the appropriate assembly configurations in the solution.

In addition, for those who have tried other works, I also tried the following with no luck:

• Create a post build event to copy files instead of hacking an XML project file. I could not get this to work because I could not form the correct path to the post-deployment script file. This connection problem describes the problem.

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

Answer 2

Here's how I handle conditional deployment in the post-deployment process to deploy test data for the Debug configuration, but not Release.

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

Name the file Debug.sqlcmdvars .

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

Repeat this process to create Release.sqlcmdvars, setting $(BuildConfiguration) for release.

Now configure your configurations: Open the project properties page on the "Expand" tab. In the top drop-down list, configure as Debug. In the bottom drop-down list (Sql command variables) set the file in Properties \ Debug.sqlcmdvars.

Repeat for release as: In the upper drop-down list, set the configuration for release. In the bottom drop-down list (Sql command variables) set the file to Properties \ Release.sqlcmdvars.

Now, in your Script.PostDeployment.sql file, you can specify conditional logic, for example:

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

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

Now you can: now your developer builds contain test data, but not your release builds!

Answer 3

I managed to solve the problem using the noexec method.

So instead:

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

I canceled the condition and set NOEXEC ON to skip the imported statement (s):

 IF ('$(ConfigSetting)' <> 'Configuration1') SET NOEXEC ON :r .\Configuration1\Data.sql SET NOEXEC OFF 

Make sure you cancel it if you want to fulfill any subsequent statements.

Answer 4

As Rob found out, GO statements are not allowed in linked SQL scripts, as this may embed it in BEGIN / END statements.

However, I have another solution for it - if possible, remove any GO statements from the reference scripts and put one of them after the END statement:

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

Note that I also created a new variable in my sqlcmdvars file called $ (DeployTestData), which allows me to enable / disable the testing script.

Answer 5

I found a hack MSDN blog that worked pretty well. The trick is to write commands to the temp script file and then execute this script. Basically equivalent to 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 

Then the TempScript.sql file will contain:

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

or

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

depending on the value of $(ConfigSetting) and when executing GO commands, etc. there will be no problems.

Answer 6

I was inspired by the decision of Rob Bird. However, I just use Build Events to replace post-deployment scripts based on the selected build configuration.

• I have one empty deployment of a "dummy" post script.
• I set up a pre-build event to replace this "dummy" file based on the selected build configuration (see attached image).
• I set up a post-build event to host the "dummy" file after the build is complete (see attached image). The reason is that I do not want to create changes in change management after assembly.