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

问题描述

我使用SQL 2008数据库项目（在visual studio中）来管理我的项目的模式和初始测试数据。 atabase项目使用一个post部署，其中包括使用SQLCMD的：r语法的许多其他脚本。

我想能够有条件地包括基于SQLCMD变量。这将允许我运行该项目几次与我们的夜间构建设置各种版本的数据库与不同的数据配置（对于多租户系统）。

我试过以下：

  IF（'$（ConfigSetting）'='Configuration1'）
 BEGIN 
 print'正在插入特定配置'
：r .\Configuration1\Data.sql 
 END 
 ELSE 
 BEGIN 
 print'inserted generic data'
：r .\GenericConfiguration \Data.sql 
 END 
  

得到编译错误：
SQL01260：发生致命的解析器错误：Script.PostDeployment.sql

有人看到此错误或管理来配置他们的postdeployment脚本是灵活的这样吗？

谢谢，
Rob

我也试过改变这个，使文件的路径是一个变量，类似于此贴。

我现在发现上面的if / else语法不适用于我，因为我的一些链接脚本需要一个GO语句。

如果你需要一个GO语句在链接的脚本（如我一样），那么就不会有'除此之外，我最终创建了几个后部署脚本，然后更改我的项目，以在构建时覆盖主要的后部署脚本，具体取决于构建配置。

对于任何需要同样东西的人 - 我发现此信息有用

所以在我的项目中我有以下post部署文件：

• Script.PostDeployment.sql ）


• Default.Script.PostDeployment.sql（指向标准数据配置所需的脚本）


• Configuration1.Script.PostDeployment.sql特定数据配置所需的脚本）

然后我将以下内容添加到项目文件的末尾（右键单击以卸载，然后右键编辑）：

 < Target Name =BeforeBuild> 
< Message Text =复制正在运行的配置文件任务：$（Configuration）Importance =high/> 
< Copy Condition ='$（Configuration）'=='Release'SourceFiles =Scripts\Post-Deployment\Default.Script.PostDeployment.sqlDestinationFiles =Scripts\Post-Deployment \Script.PostDeployment.sqlOverwriteReadOnlyFiles =true/> 
< Copy Condition ='$（Configuration）'=='Debug'SourceFiles =Scripts\Post-Deployment\Default.Script.PostDeployment.sqlDestinationFiles =Scripts\Post-Deployment \Script.PostDeployment.sqlOverwriteReadOnlyFiles =true/> 
< Copy Condition ='$（Configuration）'=='Configuration1'SourceFiles =Scripts\Post-Deployment\Configuration1.Script.PostDeployment.sqlDestinationFiles =Scripts\Post-Deployment \Script.PostDeployment.sqlOverwriteReadOnlyFiles =true/> 
< / Target> 
  

最后，您需要在解决方案中设置匹配的构建配置。

此外，对于任何尝试其他工作的人，我也试过下面没有运气：

1. 创建后期构建事件以复制文件，而不是必须破解项目文件XML。我不能得到这个工作，因为我不能形成正确的路径后部署脚本文件。 此连接问题描述问题




2. 使用脚本路径的变量传递给：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.

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.

解决方案

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.

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