MSBuild.Sdk.SqlProj: Schema project is not taken into account during script creation

We have a setup with various database projects, usually always in the following combination.

  • Database.csproj Contains all database configurations, the schema, users, views etc. and is used in the deployment to roll out the database changes.
<Project Sdk="MSBuild.Sdk.SqlProj/2.7.2">
  <PropertyGroup>
    <TargetFramework>netstandard2.0</TargetFramework>
    <SqlServerVersion>Sql160</SqlServerVersion>
    <!-- For additional properties that can be set here, please refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#model-properties -->
  </PropertyGroup>
</Project>

  • Database.TestValues.csproj Used to seed test cases based on the schema for our automation test scripts.
<Project Sdk="MSBuild.Sdk.SqlProj/2.7.2">
  <PropertyGroup>
    <TargetFramework>netstandard2.0</TargetFramework>
    <SqlServerVersion>Sql160</SqlServerVersion>
    <!-- For additional properties that can be set here, please refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#model-properties -->
  </PropertyGroup>

  <PropertyGroup>
    <GenerateCreateScript>True</GenerateCreateScript>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>

    <TargetDatabaseName>foo</TargetDatabaseName>
    <RunScriptsFromReferences>True</RunScriptsFromReferences>
  </PropertyGroup>

  <ItemGroup>
    <None Include="HelperScripts\**" />
    <PostDeploy Include="Post-Deployment\Script.PostDeployment.sql" />
  </ItemGroup>

  <ItemGroup>
    <ProjectReference Include="..\Database\Database.csproj" />
  </ItemGroup>

</Project>

As long as we use <Project Sdk="MSBuild.Sdk.SqlProj/2.6.1">, the foo_Create.sql contains both the schema and the test data. As soon as we upgrade this to a version 2.7.x, the foo_Create.sql only contains test data, which leads to errors as there is no schema on the test containers.

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Comments: 38 (4 by maintainers)

Most upvoted comments

I think the difference in how the reference appears in the Model.xml is interesting as @ErikEJ pointed out earlier. Not sure what’s causing that though. Nothing has changed on our side as far as I know, so it must be DacFx handling that differently. That being said, the reference should still be valid because we should be copying the referenced .dacpac to the output folder anyway.

@samtrion Have you tried doing a dotnet publish instead of using the GenerateCreateScript option? Maybe that will shed some light on what’s going wrong here.