Project References - `dotnet build/msbuild` + ProjectReference database objects

[michael-huxtable]

Hi,

I've been enjoying this project so far and the ability to build these database projects using dotnet build and docker. I've been trying to migrate a couple of projects using project references over to using this project but when trying to build within docker using dotnet build or dotnet msbuild I get the following project reference error:

Microsoft (R) Build Engine version 16.5.0+d4cbfca49 for .NET Core
Copyright (C) Microsoft Corporation. All rights reserved.

C:\Program Files\dotnet\sdk\3.1.200\Microsoft.Common.CurrentVersion.targets(2081,5): error MSB4018: The "ResolveAssemblyReference" task failed unexpectedly. []
C:\Program Files\dotnet\sdk\3.1.200\Microsoft.Common.CurrentVersion.targets(2081,5): error MSB4018: System.InvalidOperationException: PE image does not have metadata. []
C:\Program Files\dotnet\sdk\3.1.200\Microsoft.Common.CurrentVersion.targets(2081,5): error MSB4018:    at System.Reflection.PortableExecutable.PEReader.GetMetadataBlock() []
C:\Program Files\dotnet\sdk\3.1.200\Microsoft.Common.CurrentVersion.targets(2081,5): error MSB4018:    at System.Reflection.Metadata.PEReaderExtensions.GetMetadataReader(PEReader peReader, MetadataReaderOptions options, MetadataStringDecoder utf8Decoder) []
C:\Program Files\dotnet\sdk\3.1.200\Microsoft.Common.CurrentVersion.targets(2081,5): error MSB4018:    at System.Reflection.Metadata.PEReaderExtensions.GetMetadataReader(PEReader peReader) []

Is the intention that project references will not work with dotnet build or dotnet msbuild? If so then building purely on linux is not possible at the moment when using project references.

If I build via regular msbuild, then I get errors when trying to reference the other database in other procs using the following SSDT syntax:

[$(DatabaseName)].[dbo].[TableName]

Through the test projects, I cannot see in the referenced project any procs that use database objects from the source project. I will have to create a test project to prove this if the above does is not enough information. Here is the test I am using for reference that does not reference any database objects:

https://github.com/rr-wfm/MSBuild.Sdk.SqlProj/tree/master/test/TestProjectWithProjectReference

Throughout the build I can see the referenced dacpac is passed to the build, but it seems that these database objects cannot be referenced:

Error MSB3073   The command "dotnet "C:\[omitted]\.nuget\packages\msbuild.sdk.sqlproj\1.10.0\Sdk\../tools/netcoreapp3.1/DacpacTool.dll" build -o "obj\Debug\netstandard2.0\[ProjectName].dacpac" -n "[ProjectName]" -v "1.0.0" -sv Sql140 -i "obj\Debug\netstandard2.0\[ProjectName].InputFiles.txt" -r "C:\[omitted]\{ReferencedProject}.dacpac;{ReferencedDatabaseName}" 

MSBuild.Sdk.SqlProj: 1.10.0. Affected Operating Systems: Windows and Linux

[jmezach]

Hi @michael-huxtable, thanks for reporting this. The first error message you shared seems to suggest that you're trying to reference an MSBuild.Sdk.SqlProj project from a regular .NET project. This is not supported because that would mean you would compile an assembly (.dll) against a .dacpac which obviously wouldn't work.

If that is not what you're trying or intended to do then I would ask you to share your project files (of both the project containing the reference and the project being referenced) to help us figure out what's going wrong. You also mentioned you're building in Docker, so it could be helpful to also share the Dockerfile you're using to see if that could have an impact on things.

[michael-huxtable]

Thanks for the quick reply. I've put a sample project together and added it to my github here:

https://github.com/michael-huxtable/MSBuild.Sdk.SqlProj.ProjectReference

In the example, I have 2 buddy projects to build both of the new csproj databases, and I am not referencing the sqlproj files that I can see. If you run the following from the build root:

docker build -t database --build-arg Configuration=Release -f .\TargetDatabase.Build\Dockerfile ./

Then you should get the following:

Step 9/15 : RUN dotnet build "./TargetDatabase.Build/TargetDatabase.Build.csproj" -c $Configuration -o /app/build
 ---> Running in 25a7b4f128c8
Microsoft (R) Build Engine version 16.7.1+52cd83677 for .NET
Copyright (C) Microsoft Corporation. All rights reserved.

  Determining projects to restore...
  Restored /TargetDatabase.Build/TargetDatabase.Build.csproj (in 1.57 sec).
  Restored /SourceDatabase.Build/SourceDatabase.Build.csproj (in 1.57 sec).
  Using package name SourceDatabase.Build and version 1.0.0
  Using SQL Server version Sql140
  Adding /SourceDatabase/Users.sql to the model
  Writing model to /SourceDatabase.Build/obj/Release/netstandard2.0/SourceDatabase.Build.dacpac
  SourceDatabase.Build -> /app/build/SourceDatabase.Build.dacpac
  Using package name TargetDatabase.Build and version 1.0.0
  Using SQL Server version Sql140
  Adding reference to /app/build/SourceDatabase.Build.dacpac
  Adding /TargetDatabase/UsersView.sql to the model
/TargetDatabase/UsersView.sql(2,19): ModelValidationError error SQL71561: SqlView: [dbo].[UsersView] has an unresolved reference to object [$(SourceDatabase)].[dbo].[Users]. [/TargetDatabase.Build/TargetDatabase.Build.csproj]
/TargetDatabase/UsersView.sql(2,12): ModelValidationError warning SQL71563: The wildcard character in $(SourceDatabase).dbo.Users could not be expanded. [/TargetDatabase.Build/TargetDatabase.Build.csproj]
  Found 1 error(s), skip building package
/root/.nuget/packages/msbuild.sdk.sqlproj/1.10.0/Sdk/Sdk.targets(196,5): error MSB3073: The command "dotnet "/root/.nuget/packages/msbuild.sdk.sqlproj/1.10.0/Sdk/../tools/netcoreapp3.1/DacpacTool.dll" build -o "obj/Release/netstandard2.0/TargetDatabase.Build.dacpac" -n "TargetDatabase.Build" -v "1.0.0" -sv Sql140 -i "obj/Release/netstandard2.0/TargetDatabase.Build.InputFiles.txt" -r "/app/build/SourceDatabase.Build.dacpac;"      " exited with code 1. [/TargetDatabase.Build/TargetDatabase.Build.csproj]

Build FAILED.

/TargetDatabase/UsersView.sql(2,12): ModelValidationError warning SQL71563: The wildcard character in $(SourceDatabase).dbo.Users could not be expanded. [/TargetDatabase.Build/TargetDatabase.Build.csproj]
/TargetDatabase/UsersView.sql(2,19): ModelValidationError error SQL71561: SqlView: [dbo].[UsersView] has an unresolved reference to object [$(SourceDatabase)].[dbo].[Users]. [/TargetDatabase.Build/TargetDatabase.Build.csproj]
/root/.nuget/packages/msbuild.sdk.sqlproj/1.10.0/Sdk/Sdk.targets(196,5): error MSB3073: The command "dotnet "/root/.nuget/packages/msbuild.sdk.sqlproj/1.10.0/Sdk/../tools/netcoreapp3.1/DacpacTool.dll" build -o "obj/Release/netstandard2.0/TargetDatabase.Build.dacpac" -n "TargetDatabase.Build" -v "1.0.0" -sv Sql140 -i "obj/Release/netstandard2.0/TargetDatabase.Build.InputFiles.txt" -r "/app/build/SourceDatabase.Build.dacpac;"      " exited with code 1. [/TargetDatabase.Build/TargetDatabase.Build.csproj]
    1 Warning(s)
    2 Error(s)

Time Elapsed 00:00:15.18
The command '/bin/sh -c dotnet build "./TargetDatabase.Build/TargetDatabase.Build.csproj" -c $Configuration -o /app/build' returned a non-zero code: 1

I can't build the solution in VS2019, but at least the Dockerfile should be consistent.

[jmezach]

Looking at those errors I think they might be caused by #39. It looks like the build is failing to resolve the references to the objects in the referenced project because the SQLCMD variable used to reference them isn't passed at build time. I don't think that issue would be hard to fix, we just haven't gotten around to implementing it yet.

[michael-huxtable]

In this example, is there a way to workaround the SQLCMD variable and just access the DB name directly? Sorry if I assumed that the project references would allow this sort of behaviour at the moment.

[jmezach]

After looking at your sample project I'm thinking the issue might be caused by the project reference being defined as:

<ProjectReference Include="..\SourceDatabase.Build\SourceDatabase.Build.csproj" DatabaseLiteralValue="SourceDatabase" />

I think that DatabaseLiteralValue should be replaced with DatabaseVariableLiteralValue to make this work. Could you try that to see if that resolves your issue?

[michael-huxtable]

Ah yes! apologies. To get this working then, I had to:

• Remove the database variable name for the database project reference
• Update the view to reference the other database directly

At this point, the newer csproj file builds but then the older sqlproj does not due to being unable to reference that database name, but that should be enough to get me moved over to the new format!

Thanks

[jmezach]

Alright, I'll go ahead and close this issue. Feel free to re-open if you still feel there's an issue here.