Schema project is not taken into account during script creation about msbuild.sdk.sqlproj HOT 38 CLOSED

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.

from msbuild.sdk.sqlproj.

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

from msbuild.sdk.sqlproj.

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

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

from msbuild.sdk.sqlproj.

@samtrion So the build fails???

from msbuild.sdk.sqlproj.

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

from msbuild.sdk.sqlproj.

@samtrion So the build fails???

No build is fine, dotnet test goes on error instant

from msbuild.sdk.sqlproj.

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.

from msbuild.sdk.sqlproj.

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

from msbuild.sdk.sqlproj.

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.

from msbuild.sdk.sqlproj.

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

from msbuild.sdk.sqlproj.

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.

from msbuild.sdk.sqlproj.

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.

from msbuild.sdk.sqlproj.

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.

from msbuild.sdk.sqlproj.

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>

from msbuild.sdk.sqlproj.

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?

from msbuild.sdk.sqlproj.

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

from msbuild.sdk.sqlproj.

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

from msbuild.sdk.sqlproj.

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

from msbuild.sdk.sqlproj.

@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>

from msbuild.sdk.sqlproj.

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.

from msbuild.sdk.sqlproj.

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.

from msbuild.sdk.sqlproj.

@jmezach Thoughts?

from msbuild.sdk.sqlproj.

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

from msbuild.sdk.sqlproj.

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

from msbuild.sdk.sqlproj.

@samtrion Did you try dotnet publish or sqlpackage publish?

from msbuild.sdk.sqlproj.

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.

from msbuild.sdk.sqlproj.

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

from msbuild.sdk.sqlproj.

I'm trying it right now

from msbuild.sdk.sqlproj.

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.

from msbuild.sdk.sqlproj.

Looking forward to hearing more

from msbuild.sdk.sqlproj.

It seems that the new version of DacFx has reduced the fault tolerance. We had a missing part IF EXISTS in one of our statements, in addition to our problems with the SQL script creation. Unfortunately, this only became visible in the logfile when we started using https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dac.dacservices.publish?view=sql-dacfx-162.

from msbuild.sdk.sqlproj.

This sounds like we are missing some error handling and diagnostics then?

from msbuild.sdk.sqlproj.

Basically, the error descriptions for each of the attempted implementation approaches pointed out an error. However, the informations from DacServices.Publish is a bit more accurate. Pure perception on my part.

from msbuild.sdk.sqlproj.

@jmezach You mean when calling GenerateSctipt?

from msbuild.sdk.sqlproj.

@ErikEJ Yeah, that's what I was thinking. If the Publish fails with a clear error then why doesn't that same error occur when generating a script? Unless of course it is dependent on the target database.

from msbuild.sdk.sqlproj.

I am a little concerned that the .dacpac generated in the repro project could not be extracted, indicating that it was not a valid package. (the .TestValues package)

from msbuild.sdk.sqlproj.

@samtrion is my understanding correct that when you fixed the script error, you were able to use the newest SDK version ?

from msbuild.sdk.sqlproj.

@ErikEJ Yes, that is correct.

from msbuild.sdk.sqlproj.