使用 SQLCMD 的 PostDeployment.sql 脚本中的条件逻辑 [英] Conditional logic in PostDeployment.sql script using SQLCMD

问题描述

我正在使用 SQL 2008 数据库项目(在 Visual Studio 中)来管理我的项目的架构和初始测试数据.atabase 项目使用后部署，其中包括许多使用 SQLCMD 的:r"语法的其他脚本.

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.

我希望能够根据 SQLCMD 变量有条件地包含某些文件.这将允许我在每晚构建时多次运行该项目，以设置具有不同数据配置的不同版本的数据库(对于多租户系统).

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).

我尝试了以下方法:

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

但是我得到一个编译错误:SQL01260:发生了致命的解析器错误:Script.PostDeployment.sql

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?

谢谢，罗布

附言我也试过改变这一点，以便文件的路径是一个变量，类似于这篇文章.但这给了我一个错误，说路径不正确.

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.

推荐答案

UPDATE

我现在发现上面的 if/else 语法对我不起作用，因为我的一些链接脚本需要 GO 语句.本质上 :r 只是内联导入脚本，因此这成为无效的语法.

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.

如果您需要链接脚本中的 GO 语句(就像我一样)，那么没有任何简单的方法可以解决这个问题，我最终创建了几个部署后脚本，然后更改了我的项目以覆盖主要的部署后脚本构建时间取决于构建配置.这现在正在做我需要的，但似乎应该有更简单的方法！

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(将被替换的空文件)
• Default.Script.PostDeployment.sql(链接到标准数据配置所需的脚本)
• Configuration1.Script.PostDeployment.sql(特定数据配置所需脚本的链接)

然后我将以下内容添加到项目文件的末尾(右键单击卸载然后右键单击编辑):

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. 创建一个构建后事件来复制文件，而不必破解项目文件 XML.我无法让它工作，因为我无法形成部署后脚本文件的正确路径.此连接问题描述了问题

使用脚本路径的变量传递给 :r 命令.但是我遇到了这种方法的几个错误.

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

这篇关于使用 SQLCMD 的 PostDeployment.sql 脚本中的条件逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！