使用 SQLCMD 的 PostDeployment.sql 脚本中的条件逻辑

Conditional logic in PostDeployment.sql script using SQLCMD

提问by Rob Bird

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.

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

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

我希望能够有条件地包含基于 SQLCMD 变量的某些文件。这将允许我在夜间构建中多次运行该项目，以设置具有不同数据配置的数据库的不同版本（对于多租户系统）。

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

但我收到一个编译错误： SQL01260：发生致命的解析器错误：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.

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

采纳答案by Rob Bird

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/else 语法对我不起作用，因为我的一些链接脚本需要 GO 语句。基本上 :r 只是内联导入脚本，因此这成为无效的语法。

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!

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

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)

• 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="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.

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

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

回答by Simon Calvin

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

我设法使用noexec 方法解决了这个问题。

So, instead of this:

所以，而不是这个：

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

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

我反转了条件并将NOEXEC ON设置为跳过导入的语句：

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.

如果要执行任何后续语句，请确保将其关闭。

回答by Rob McCauley

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

以下是我在部署后过程中处理条件部署的方式，以便为 Debug 而不是 Release 配置部署测试数据。

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

首先，在解决方案资源管理器中，打开项目属性文件夹，然后右键单击以添加一个新的 SqlCmd.variables 文件。

Name the file Debug.sqlcmdvars.

命名文件Debug.sqlcmdvars。

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

在该文件中，添加您的自定义变量，然后添加一个名为 的最终变量$(BuildConfiguration)，并将值设置为 Debug。

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

重复该过程以创建 Release.sqlcmdvars，将 设置$(BuildConfiguration)为 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.

现在，配置您的配置：打开项目属性页面到 Deploy 选项卡。在顶部下拉列表中，将配置设置为 Debug。在底部下拉菜单（Sql 命令变量）中，将文件设置为 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.

重复发布为：在顶部下拉列表中，将配置设置为发布。在底部下拉菜单（Sql 命令变量）中，将文件设置为 Properties\Release.sqlcmdvars。

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

现在，在您的 Script.PostDeployment.sql 文件中，您可以指定条件逻辑，例如：

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.

在解决方案资源管理器中，右键单击顶级解决方案并打开配置管理器。您可以指定哪个配置对您的构建有效。您还可以在 MSBUILD.EXE 命令行上指定配置。

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

好了 - 现在您的开发人员构建有测试数据，但没有您的发布构建！

回答by Dunc

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

正如 Rob 所研究的那样，链接的 SQL 脚本中不允许使用 GO 语句，因为这会将其嵌套在 BEGIN/END 语句中。

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:

但是，我有一个不同的解决方案 - 如果可能，从引用的脚本中删除任何 GO 语句，并在 END 语句之后放置一个：

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 sqlcmdvarsfile called $(DeployTestData) which allows me to turn on/off test script deployment.

请注意，我还在我的sqlcmdvars文件中创建了一个名为 $(DeployTestData)的新变量，它允许我打开/关闭测试脚本部署。

回答by kjbartel

I found a hack from an MSDN blogwhich 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.

我从 MSDN 博客中发现了一个hack，它运行得相当好。诀窍是将命令写入临时脚本文件，然后执行该脚本。基本上相当于 SQLCMD 的动态 SQL。

-- 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.sqlfile will then contain either:

TempScript.sql然后该文件将包含：

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 GOstatements etc. when it is executed.

取决于 和 的值，执行时$(ConfigSetting)不会出现GO语句等问题。

回答by Martin Karou?

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.

我的灵感来自 Rob Bird 的解决方案。但是，我只是使用构建事件来替换基于所选构建配置的部署后脚本。

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.

1. 我有一个空的“虚拟”部署后脚本。
2. 我设置了一个预构建事件来根据选定的构建配置替换这个“虚拟”文件（见附图）。
3. 我设置了一个构建后事件，在构建完成后将“虚拟”文件放回原处（见附图）。原因是我不想在构建后在更改控件中生成更改。