在sql postdeployment生成脚本中使用变量? [英] Using variable in sql postdeployment build script?

问题描述

我想做的是能够在Visual Studio数据库项目中创建一个发布xml脚本，该脚本定义一个变量，该变量将用于编写脚本.

我的问题是，我得到一个错误:"SQL72008:未定义变量DeployType."如果我没有在部署后脚本中定义变量，例如:setvar DeployType" varchar(100)"

My problem is that I get a Error: "SQL72008: Variable DeployType is not defined." if I don´t define the variable in the post deployment script like this ":setvar DeployType "varchar(100)"

当我运行publish.xml时，已在生成的脚本 BUT 的顶部正确设置了DeployType参数，该值被:setvar DeployType" varchar(100)覆盖.为此，我需要在运行此脚本之前手动删除该行.

When I run the publish.xml the DeployType parameter is rightly set at the top of the generated script BUT that value get overridden with the ":setvar DeployType "varchar(100)". So to make this work I would need to manually remove that line before running this script.

所以问题是如何在不默认部署后脚本中的setvar变量的情况下构建项目并进行发布?

这是我的PostDeployment.sql文件的内容，该文件在没有默认DeployType变量的情况下不会生成

This is the content of my PostDeployment.sql file that does not build without defaulting the DeployType variable

--The line causing my problems. I would like to skip it somehow.
:setvar DeployType "varchar(100)"

Print 'Always include core'
:r ..\Data\Core\_BaseCore.sql

--Conditionaly add based on DeployType comming from the publishing.xml
IF ('$(DeployType)' = 'A')
BEGIN
:r ..\Data\A\_BaseKnap.sql
END
ELSE IF ('$(DeployType)' = 'B')
BEGIN
:r ..\Data\B\_BaseB.sql
END

这是Database.publish.xml文件中的内容

This is the content in the Database.publish.xml file

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
 <PropertyGroup>
   <IncludeCompositeObjects>True</IncludeCompositeObjects>
   <TargetDatabaseName>db name</TargetDatabaseName>
   <DeployScriptFileName>Database.sql</DeployScriptFileName>
   <ProfileVersionNumber>1</ProfileVersionNumber>
   <TargetConnectionString>Connection string</TargetConnectionString>
 </PropertyGroup>
 <ItemGroup>
   <SqlCmdVariable Include="DeployType">
     <Value>B</Value>
   </SqlCmdVariable>
 </ItemGroup>
</Project>

当我将其发布到脚本文件时，这是生成的脚本，将针对我得到的数据库运行(不需要删除很多东西).

When I publish this to a script file this is the generated script to be run against the database I get (lots of stuff removed not needed).

:setvar DeployType "B"

--This nulls out the DeployType set before
:setvar DeployType "varchar(100)"

Print 'Always include core'
:r ..\Data\Core\_BaseCore.sql

--Conditionaly add based on DeployType comming from the publishing.xml
IF ('$(DeployType)' = 'A')
BEGIN
:r ..\Data\A\_BaseKnap.sql
END
ELSE IF ('$(DeployType)' = 'B')
BEGIN
:r ..\Data\B\_BaseB.sql
END

运行此代码(例如在Sql Manager中)，结果是

And running this (in e.g Sql Manager) the result is

Print 'Always include core'

由于已被默认设置，因此不包含在IF语句中.

where it doesn't go into the IF statements because it has been defaulted.

希望这很清楚.

推荐答案

我知道这是一个旧线程，但这是答案:

I know this is a bit old thread, but here is the answer:

单击数据库项目上的属性，然后导航到"SQLCMD变量"窗口.在SQLCMD变量表中定义您的$(DeployType)变量.这将允许项目进行编译，因此您可以(并且实际上需要)从脚本本身中删除行:setvar DeployType "varchar(100)".

Click the properties on you database project and navigate to the "SQLCMD Variables" window. Define your $(DeployType) variable in the SQLCMD Variables table. This will allow the project to compile and hence you can (and actually need) remove the line :setvar DeployType "varchar(100)" from the script itself.

这篇关于在sql postdeployment生成脚本中使用变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！