Schema project is not taken into account during script creation

[samtrion]

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.

[samtrion]

We receive the following diagnostics.

Error MSB3073 The command "dotnet "C:\Users\samtrion\.nuget\packages\msbuild.sdk.sqlproj\2.7.2\Sdk\../tools/net7.0/DacpacTool.dll" build -o "obj\Debug\netstandard2. 0\Database.TestValues.dacpac" -n "Database.TestValues" -v "1.0.0" -sv Sql160 -i "obj\Debug\netstandard2.0\Database.TestValues.InputFiles. txt" -r "C:\source\mercedes\dlm-backend\database\Database\bin\Debug\netstandard2.0\Database.dacpac;dbl=|dbv=|srv=;" -dp IncludeCompositeObjects=True --postdeploy Post-Deployment\Script. PostDeployment.sql --generatecreatescript -tdn "dlm" " exited with code -532462766. Database.TestValues C:\Users\samtrion\.nuget\packages\msbuild.sdk.sqlproj\2.7.2\Sdk\Sdk.targets 244	

Warning SQL72025 No file was provided for the reference 'Database.dacpac'. An error may have occurred during deployment. When the package was created, the original file referenced was under 'Database.dacpac'.	Database.TestValues C:\source\mercedes\dlm-backend\database\Database.TestValues\EXEC 1

[ErikEJ]

"obj\Debug\netstandard2.0\Database.TestValues.InputFiles. txt"

This looks Odd with the extra space in the file name @jmezach ?

[ErikEJ]

@samtrion So the build fails???

[samtrion]

"obj\Debug\netstandard2.0\Database.TestValues.InputFiles. txt"

This looks Odd with the extra space in the file name @jmezach ?

I think the space comes from the copy paste desaster on my side.

[samtrion]

@samtrion So the build fails???

No build is fine, dotnet test goes on error instant

[jmezach]

We receive the following diagnostics.

Error MSB3073 The command "dotnet "C:\Users\samtrion\.nuget\packages\msbuild.sdk.sqlproj\2.7.2\Sdk\../tools/net7.0/DacpacTool.dll" build -o "obj\Debug\netstandard2. 0\Database.TestValues.dacpac" -n "Database.TestValues" -v "1.0.0" -sv Sql160 -i "obj\Debug\netstandard2.0\Database.TestValues.InputFiles. txt" -r "C:\source\mercedes\dlm-backend\database\Database\bin\Debug\netstandard2.0\Database.dacpac;dbl=|dbv=|srv=;" -dp IncludeCompositeObjects=True --postdeploy Post-Deployment\Script. PostDeployment.sql --generatecreatescript -tdn "dlm" " exited with code -532462766. Database.TestValues C:\Users\samtrion\.nuget\packages\msbuild.sdk.sqlproj\2.7.2\Sdk\Sdk.targets 244	

Warning SQL72025 No file was provided for the reference 'Database.dacpac'. An error may have occurred during deployment. When the package was created, the original file referenced was under 'Database.dacpac'.	Database.TestValues C:\source\mercedes\dlm-backend\database\Database.TestValues\EXEC 1

Is this all the output you are getting? If so, then perhaps you can share a binary log with us so we can further investigate this issue.

[ErikEJ]

I think we need to see a repro including your build script in order to understand what is going on.

[samtrion]

I think we need to see a repro including your build script in order to understand what is going on.

I will provide an issue repo this evening.

[ErikEJ]

Excellent. I assume a workaround is to use the older SDK.

[samtrion]

Unfortunately, this is something that is burning under our fingers. Since we would like to use the CodeAnalysis feature, and also because dependabot is currently not working properly with this reference.

[ErikEJ]

Well, this is a free open source project, and we do not offer paid support.

But with a runnable repro we can most likely help.

[samtrion]

Which is understandable, so I hope I can contribute a bit to the quality and stability with the bug report and repo https://github.com/samtrion/sqlproj-issue-557.

[ErikEJ]

Model.xml from 2.7.2:

<?xml version="1.0" encoding="utf-8"?>
<DataSchemaModel FileFormatVersion="1.2" SchemaVersion="2.4" DspName="Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider" CollationLcid="1033" CollationCaseSensitive="False" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">
	<Header>
		<CustomData Category="Reference" Type="SqlSchema">
			<Metadata Name="FileName" Value="Database.dacpac" />
			<Metadata Name="LogicalName" Value="Database.dacpac" />
			<Metadata Name="SuppressMissingDependenciesErrors" Value="False" />
		</CustomData>
	</Header>
	<Model>
		<Element Type="SqlDatabaseOptions">
			<Property Name="Collation" Value="SQL_Latin1_General_CP1_CI_AS" />
			<Property Name="IsAnsiNullDefaultOn" Value="True" />
			<Property Name="IsAnsiNullsOn" Value="True" />
			<Property Name="IsAnsiWarningsOn" Value="True" />
			<Property Name="IsArithAbortOn" Value="True" />
			<Property Name="IsConcatNullYieldsNullOn" Value="True" />
			<Property Name="IsTornPageProtectionOn" Value="False" />
			<Property Name="IsFullTextEnabled" Value="True" />
			<Property Name="PageVerifyMode" Value="3" />
			<Property Name="CatalogCollation" Value="0" />
		</Element>
	</Model>
</DataSchemaModel>

Model.xml from 2.6.1:

<?xml version="1.0" encoding="utf-8"?>
<DataSchemaModel FileFormatVersion="1.2" SchemaVersion="2.4" DspName="Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider" CollationLcid="1033" CollationCaseSensitive="False" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">
	<Header>
		<CustomData Category="Reference" Type="SqlSchema">
			<Metadata Name="FileName" Value="C:\Code\Github\sqlproj-issue-557\src\Database\bin\Debug\netstandard2.0\Database.dacpac" />
			<Metadata Name="LogicalName" Value="Database.dacpac" />
			<Metadata Name="SuppressMissingDependenciesErrors" Value="False" />
		</CustomData>
	</Header>
	<Model>
		<Element Type="SqlDatabaseOptions">
			<Property Name="Collation" Value="SQL_Latin1_General_CP1_CI_AS" />
			<Property Name="IsAnsiNullDefaultOn" Value="True" />
			<Property Name="IsAnsiNullsOn" Value="True" />
			<Property Name="IsAnsiWarningsOn" Value="True" />
			<Property Name="IsArithAbortOn" Value="True" />
			<Property Name="IsConcatNullYieldsNullOn" Value="True" />
			<Property Name="IsTornPageProtectionOn" Value="False" />
			<Property Name="IsFullTextEnabled" Value="True" />
			<Property Name="PageVerifyMode" Value="3" />
			<Property Name="CatalogCollation" Value="0" />
		</Element>
	</Model>
</DataSchemaModel>

[ErikEJ]

Looks like a DacFX change:

https://github.com/rr-wfm/MSBuild.Sdk.SqlProj/blob/master/src/DacpacTool/Extensions.cs#L105

@samtrion I see no build error in your repo. Which exact command do you run (as already asked earlier) to get the build error?

[samtrion]

@ErikEJ Unfortunately, there is no build error until the integration tests generate errors because the schema is not available. I will update the repo for this, incl. pipeline setup.

[ErikEJ]

@samtrion Cool, you can just upload the relevant pipeline yml tasks / steps

[samtrion]

@ErikEJ The repository has been updated.

The main branch now contains, among other things, the basics for the integration tests, which we carry out in this way. The 4 example tests are always executed first in every scenario. A simplified version of our pipeline has been added.

The PR / branch chore/upgrade-msbuild.sdk.sqlproj-2.7.2 contains the upgrade to the new version. And thus, unfortunately, the faulty pipeline execution.

[ErikEJ]

@samtrion Thanks, still unsure what the root cause is, seems like a DacFX change

In the meantime, you can consolidate in a single project to get the functionality you want (with just the "Database" project), by updating the .csproj as follows,

• move the test files to the database project and add a Test configuration to your solution and use the "Test" configuration in your pipeline

<Project Sdk="MSBuild.Sdk.SqlProj/2.7.2">
	<PropertyGroup>
		<TargetFramework>netstandard2.0</TargetFramework>
		<SqlServerVersion>Sql160</SqlServerVersion>
		<RunSqlCodeAnalysis>true</RunSqlCodeAnalysis>
		<Configurations>Debug;Release;Test</Configurations>
	</PropertyGroup>

	<PropertyGroup Condition="'$(Configuration)' == 'Test'">
		<GenerateCreateScript>True</GenerateCreateScript>
		<TargetDatabaseName>foo</TargetDatabaseName>
	</PropertyGroup>

	<ItemGroup Condition="'$(Configuration)' == 'Test'">
		<None Include="Seeds\**\*.sql" />
		<PostDeploy Include="Post-Deployment\Script.PostDeployment.sql" />
	</ItemGroup>

	<ItemGroup Condition="'$(Configuration)' != 'Test'">
	  <Content Remove="Seeds\**" />
	  <Content Remove="Post-Deployment\**" />
	  <None Remove="Seeds\**" />
	  <None Remove="Post-Deployment\**" />
	</ItemGroup>

</Project>

[samtrion]

This is certainly a temporary solution, but if I understand you correctly, the function of referenced projects is generally affected by this. This means that even a reference to master would behave differently.

[ErikEJ]

I think this is more of an issue with GenerateCreateScript - instead of relying on it, you could try to publish the Test .dacpac to create and seed the database.

[ErikEJ]

@jmezach Thoughts?

[jmezach]

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.

[ErikEJ]

@jmezach Yes, the Database.dacpac is copied as expected.

[samtrion]

In the meantime, I have tried various implementations with Microsoft.SqlServer.DacFx.

Variant 1: Works fine in the example issue repository, but in my environment this leads to errors. Like e.g. shifted order of commands or duplicated executions of commands. The curious thing about solution 1 is that it creates the script almost identically on the basis of the Dacpac. Just like in the original version, but the reference works here.

DacServices.DisableTelemetry();
var dacServices = new DacServices(ContainerFactory.ConnectionString);

var script = dacServices.GenerateDeployScript(
    DacPackage.Load("Database.TestValues.dacpac"),
    ContainerFactory.DatabaseName,
    new DacDeployOptions { IncludeCompositeObjects = true }
);
Assert.That(script, Is.Not.Null.Or.Empty);

var result = await ContainerFactory.Container.ExecScriptAsync(script).ConfigureAwait(false);
Assert.That(result.ExitCode, Is.EqualTo(0), result.Stderr);

Variant 2: Similar to variant 1.

DacServices.DisableTelemetry();
Assert.That(
    () =>
    {
        var dacServices = new DacServices(ContainerFactory.ConnectionString);
        var package = DacPackage.Load("Database.TestValues.dacpac");

        dacServices.Deploy(
            package,
            ContainerFactory.DatabaseName,
            upgradeExisting: true,
            options: new DacDeployOptions { IncludeCompositeObjects = true }
        );
    },
    Throws.Nothing
);

[ErikEJ]

@samtrion Did you try dotnet publish or sqlpackage publish?

[samtrion]

dotnet publish works fine, with the Database.csproj & Database.Seed.csproj combination. sqlpackage was not really on the radar after we found MSBuild.Sdk.SqlProj and the possibility with the Generate Script.

[ErikEJ]

Why not try https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dac.dacservices.publish?view=sql-dacfx-162 then?

[samtrion]

I'm trying it right now

[samtrion]

Why not try https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dac.dacservices.publish?view=sql-dacfx-162 then?

@ErikEJ Okay, we have found a solution. Thanks for the hint.

[ErikEJ]

Looking forward to hearing more