How to handle master database reference

[BenjaminAbt]

How to handle master database reference

[dzsquared]

Sorry for the radio silence - I will check into the redistribution of master.dacpac. As with @ErikEJ I suspect that this is a no-go but want to investigate as I understand the benefit.

[jeffrosenberg]

I also think that a reference would be the best and we would also avoid the roundtrip. And the way beyond that already exists in classic Sql projects.

@BenjaminAbt the "classic Sql projects" are able to assume the installation of SSDT, and so they solve this by referencing files that are installed along with SSDT. We can't make that same assumption for this project, so I don't think it's accurate to say this "already exists" for our purposes. But I'd also point out that even if we resolve this using a NuGet reference, NuGet would only perform the roundtrip once -- once you had the master.nupkg package, it wouldn't need to be downloaded each time.

[jmezach]

Looking at the logs I think the .dacpac isn't being resolved properly. And I think I know why. Currently we're assuming that the .dacpac file has the same name as the package ID. So you could try renaming the .dacpac file to the name of the package and see if that helps.

[jmezach]

@BenjaminAbt Thanks for the feedback, really appreciate it.

@jeffrosenberg @ErikEJ I guess we could ship an MSBuild.Sdk.SqlProj.MasterDatabase package with various major versions matching the SQL Server versions so that people can reference that if they want to. That way the experience is the same for users own projects and the master database, rather than having a different way of doing things for the master database as @jeffrosenberg mentioned (see #64 (comment)).

I'm still not convinced though that is the right way to go about this. Conceptually I'm thinking that various SQL Server versions are a similar concept to the different versions of .NET which in the .NET world is modelled using target frameworks. Essentially the master database is a platform that exposes an API based on the version of SQL Server you're targeting. But you won't always need the master database in this case, so having a toggle to include/exclude it makes sense.

And of course we still need to decide whether we want to risk redistributing these master.dacpac files as part of this package (or another package for that matter).

[brettrowberry]

But this (dependency to VS / VSBT) is in itself a requirement that you have with classic SQL projects anyway. So at least from this point of view this would not be a deterioration of the requirement at all.

To me, this project is about making it possible to not use Windows and Visual Studio (unlike classic SQL projects) - the only requirement is the .NET SDK.

[brettrowberry]

@dzsquared I would really love to be able to reference NuGet packages for SQL Server system databases. Our apps are built and run in Docker containers. SQL Server with its Windows-only dependencies looks less and less appealing compared to PostgreSQL all the time.

[jmezach]

@pauldj54 That is great to hear. I'm glad you got this working although to be honest I wish you as a user shouldn't have to worry about this in the first place. I'd rather ship a separate NuGet package as part of this project that would contain the master.dacpac for various versions of SQL Server that you could just reference and it would work. Unfortunately there is a potential licensing issue there that we'd have to solve first as can be seen in the previous discussion in this issue.

@ErikEJ @jeffrosenberg I do feel there's a growing need for this. Perhaps we should just do it and see what happens ;)

[Sirozha1337]

There is a preview version of an official Microsoft solution available here: https://github.com/microsoft/DacFx/blob/main/src/Microsoft.Build.Sql/README.md
Maybe someone from this project can give them a hand in pushing it closer to the finish line.

[ErikEJ]

Given that master.dacpac is only installed with Visual Studio and Visual Studio Build Tools, I think it is a no-go.

[jeffrosenberg]

For a given version of SQL Server, does every instance of SQL Server have the exact same master database?

Yes, the master, model, and msdb databases are shipped along with the SQL Server binaries and created at installation. Of course, users can modify these databases on a particular server after installation, but the base database definition is always the same.

[ErikEJ]

Wonder how this is done in the Azure Data Studio extension for .sqlproj?

[ErikEJ]

With the Azure Data Studio extension installed, this folder is present (on all platforms)

C:\Users\ErikEJ\.azuredatastudio\extensions\microsoft.sql-database-projects-0.5.1\BuildDirectory\SystemDacpacs

[ErikEJ]

@orloffm but this tool is a Nuget package !?

[orloffm]

Sorry, I meant - supporting the local master.dacpac in my DB project that I'm trying to build, without the need for a separate package. Maybe that's my personal professional deformation, but we'd prefer to have it that way in our project.

Anyway, I've made the <PackageReference> approach work the following way:

Project with .dacpac files:

tools/MyOwnMasterDacpac.dacpac
MyOwnMasterDacpac.csproj

MyOwnMasterDacpac.csproj contents:

<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
  <TargetFramework>netstandard2.0</TargetFramework>
  <PackageId>MyOwnMasterDacpac</PackageId>
  <Version>12.0.4</Version>
  <Company>...</Company>
  <GeneratePackageOnBuild>true</GeneratePackageOnBuild>
  <Description>...</Description>
</PropertyGroup>

<ItemGroup>
  <None Include="tools\MyOwnMasterDacpac.dacpac" Pack="true" PackagePath="tools" />
</ItemGroup>
</Project>

Building the .nupkg:

dotnet build

Consumer project:

<Project Sdk="MsBuild.Sdk.SqlProj/1.11.4">
<PropertyGroup>
  <TargetFramework>netstandard2.0</TargetFramework>
  <SqlServerVersion>Sql120</SqlServerVersion>
</PropertyGroup>

<ItemGroup>
  <PackageReference Include="MyOwnMasterDacpac" Version="12.0.4" />
</ItemGroup>
</Project>

Then during build you can see in the logs that it is included.

Thanks.

[pinkfloydx33]

@philipobriensgs Yeah, after looking into the tooling in this repo, I'm pretty sure that's what you did. Anyways, shortly after I posted, I tried building on Linux instead and now I can deploy the DAC from both windows and Linux. While I prefer this route, it's a little disappointing it won't work the other way around.

While this is more of a SqlPackage issue, it's resolvable by using forward slashes for the reference arguments (which will work on Linux and Windows just fine). So I think this repo can make the change here:

MSBuild.Sdk.SqlProj/src/MSBuild.Sdk.SqlProj/Sdk/Sdk.targets

Line 211 in 666f55e

<ReferenceArguments>@(DacpacReference->'-r &quot;%(DacpacFile);%(ExternalParts)&quot;', ' ')</ReferenceArguments>

or maybe

MSBuild.Sdk.SqlProj/src/MSBuild.Sdk.SqlProj/Sdk/Sdk.targets

Line 135 in 666f55e

<DacpacReference Include="@(_ResolvedPackageReference);@(_ResolvedProjectReference)" Condition="Exists(%(DacpacFile))" />

into something that normalizes all back-slashes into forward-slashes. My MSBuild-Foo isn't what it used to be though, so I'm not sure which one makes more sense. I'll open a separate issue about it I guess.

[jmezach]

@asbjornb I think I know why it is not working. We are specifically looking for a .dacpac file that has the same name as the ID of the package being referenced inside of the tools\ folder. Looks like your package has the ID master.dacpac so we are looking for tools\master.dacpac.dacpac, which obviously doesn't exist. Try renaming the package to just master, that should work.

[dzsquared]

@jmezach - the system dacpacs (master and msdb) are now available in a nuget, I hope this helps!
https://www.nuget.org/packages/Microsoft.SqlServer.Dacpacs

While we are working on Microsoft.Build.Sql over at https://github.com/microsoft/dacfx, we are hoping many items we work on also benefit this project.

[dzsquared]

Glad it helps, huge credit to the team for getting it done - especially @zijchen

[jeffrosenberg]

Hi @BenjaminAbt - the 1.6.0 release added support for PackageReferences to external databases. To do this, you add a PackageReference element with the attribute DatabaseVariableLiteralValue set to the name of the target database. For example:

<PackageReference Include="master" Version="130.0.0" DatabaseVariableLiteralValue="master" />

However, there's one caveat to this approach. Per #40, the project currently only supports references to other projects via PackageReference, and the PackageReference is expected to be a valid NuGet package. The way my company has solved this internally is to take the master.dacpac shipped with the DAC Framework, wrap it up as a .nupkg, and publish that to our internal NuGet repository -- which is what you see referenced in the example above. Thus, my example above works for me personally, but won't work for others without some additional legwork to convert the master.dacpac into a NuGet package.

[jeffrosenberg]

@jmezach @ErikEJ I think this is a relatively-common problem for users -- at least, we've heard about this issue several times, including from myself with #51. It would be technically simple for us to wrap up master.dacpac ourselves and publish it to NuGet to avoid the workaround I described above, but I don't know much about the licensing involved. I suspect that might be a problem, do either of you know?

[jmezach]

Yeah, I agree that this will become a problem as we see more adoption. I wouldn't mind to publish the master databases as NuGet packages on NuGet.org, but I'm really not sure if that is something we can do due to licensing.

I remember having a conversation about this with @ErikEJ a while ago about something similar and he mentioned that we're doing Microsoft a favor here, but that was a small thing really. @ErikEJ Do you think they would have a problem with this?

It would be awesome if someone from the Microsoft team could weigh in on this ;)

[ErikEJ]

Maybe it would be better to include as embedded ressource ,- I think there is also version issues.

[jmezach]

@ErikEJ Not sure what you mean. Do you mean including the master.dacpac as part of the DacpacTool as an embedded resource and then always include a reference to that as part of the build pipeline?

I think that'll work, although we would probably have to write the embedded resource to disk somewhere first. I guess we could use the NuGet package cache for that though.

But what version of the master.dacpac would we include? The latest one? Or is that the versioning issue you're referring too?

[ErikEJ]

Yes, just throwing out some ideas 😄

Do you mean including the master.dacpac as part of the DacpacTool as an embedded resource and then always include a reference to that as part of the build pipeline?

Something like that.

Or is that the versioning issue you're referring too?

Indeed, but we have the SqlServerVersion property available.

[jmezach]

Yeah, I think we can add a reference to the proper master.dacpac based on the SqlServerVersion specified in the project file. I'm also thinking we might not have to make it an embedded resource, because that would significantly bloat the package since we're already building and packaging the DacpacTool for multiple target frameworks (netcoreapp2.1, netcoreapp3.1 and net5.0) and we would essentially include 3 copies of all these .dacpac's.

Instead I'm thinking we could add these master.dacpac files to the MSBuild.Sdk.SqlProj package inside of the tools folder with a separate folder for each SqlServerVersion. Then we could probably add a new property to the Sdk, something like ReferenceMasterDatabase which could be a simple boolean flag that indicates whether a reference to the appropriate master.dacpac is added automagically. It shouldn't be too hard to find these relatively from the Sdk.targets file.

Still not sure whether we can redistribute these master.dacpac files though and I'm not sure how we're going to find out. Maybe we should just do it and deal with the consequences later? ;).

[BenjaminAbt]

Hi, thanks for all of your feedback!

I have seen the functionality with NuGet packages, but I was not aware that this was also used or intended for system databases. Personally I think this is rather cumbersome, because it would mean an additional roundtrip due to the NuGet package, which I cannot maintain directly in the solution (build -> deploy -> restore).

I also think that a reference would be the best and we would also avoid the roundtrip. And the way beyond that already exists in classic Sql projects.

[jeffrosenberg]

@jmezach I think that solution would be fine; the one reason I might prefer publishing a master.dacpac NuGet package would be to keep the syntax for database references consistent, rather than having a totally different attribute for references to the master database.

That's not a dealbreaker for me, though, so I'm fine with either solution, but ultimately I think the big issue here will be whether we're comfortable going ahead with this from a licensing perspective.

[BenjaminAbt]

How must the master database NuGet look like and/or be structured?

I put the master.dacpac as content into a NuGet pkg, put the pkg in local folder of the solution, references that folder to NuGet.config (to avoid publish to a NuGet server) and referred to it.
Visual Studio recognizes the NuGet package but the project still throws the error that the system tables cannot be found.

I suspect that the structure of the package is not correct.

[jmezach]

@BenjaminAbt The NuGet package must have a tools folder and then inside the tools folder there should be the master.dacpac.

Also, how did you add the reference to your package to your project? Could you share that section of the .csproj?

[BenjaminAbt]

The nuspec file:

<?xml version="1.0" encoding="utf-8"?>
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
  <metadata>
    <id>MyProject.Database.MasterAzureSQLV12</id>
    <version>1.0.0</version>
    <title></title>
    <authors>MyProject</authors>
    <owners></owners>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <description>MSSQL Master Table Azure V12</description>
  </metadata>
  <files>
    <file src="tools\master.dacpac" target="tools\master.dacpac" />
  </files>
</package>

cspoj

<Project Sdk="MSBuild.Sdk.SqlProj/1.8.1">
    <!-- https://github.com/rr-wfm/MSBuild.Sdk.SqlProj -->

    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
        <SqlServerVersion>SqlAzure</SqlServerVersion>
        <!-- For additional properties that can be set here,
        please refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#model-properties -->
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="MyProject.Database.MasterAzureSQLV12" Version="1.0.0" DatabaseVariableLiteralValue="master" />
    </ItemGroup>

    <ItemGroup>
        <Content Include="..\..\db\MyProject.Database.Mssql\dbo\**\*.sql" />
    </ItemGroup>

</Project>

Solution show everything should be recognized:

but errors like

Severity Code Description Project File Line Suppression State
Error SQL71501 SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTableA].[rows], [dbo].[MyTableB].[rows] or [sys].[partitions].[rows]. MyProject.Database.Mssql.Build

still appear.

Same behavior on command line or in Visual Studio.

[jmezach]

That looks okay to me. @jeffrosenberg Is this also how you are doing it?

@BenjaminAbt Could you share the build output you're getting? It should tell you if the reference is correctly added.

[jeffrosenberg]

Yes, that looks like what I have. I agree with confirming that the reference has been added.

@BenjaminAbt one thing you may need to do -- it's unclear from the above whether you have done this or not -- is to add the master .nupkg file to your local NuGet repository with something like this:

dotnet nuget push /path/to/MyProject.Database.MasterAzureSQLV12.1.0.0.nupkg --source /path/to/nuget/repo

[BenjaminAbt]

The package is located to a local solution folder named "packages" .
The local folder is added as source in the NuGet config

<?xml version="1.0" encoding="utf-8"?>
<configuration>

  <packageSources>
    <clear />
    <add key="Solution Store" value="./packages" />
    <add key="nuget.org" value="https://api.nuget.org/v3/index.json" />
  </packageSources>

</configuration>

this is the recommended way to deal with solution-only nuget dependencies you dont want to push anywhere.
The package itself is also correctly found and resolved by NuGet. But obviously the content is not.

Build started...
1>------ Build started: Project: MyProject.Database.Mssql.Build, Configuration: Debug Any CPU ------
1>You are using a preview version of .NET. See: https://aka.ms/dotnet-core-preview
1>Using package name MyProject.Database.Mssql.Build and version 1.0.0
1>Using SQL Server version SqlAzure
1>Adding C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Stored Procedures\Some.sql to the model
.. many more adding lines... but only sql files
1>Adding C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Stored Procedures\Other.sql to the model
1
1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(7,25): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] has an unresolved reference to object [sys].[partitions].
1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(8,25): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] has an unresolved reference to object [sys].[partitions].
1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(9,25): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] has an unresolved reference to object [sys].[partitions].
1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(7,92): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[index_id], [dbo].[MyTable2].[index_id] or [sys].[partitions].[index_id].
1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(8,90): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[index_id], [dbo].[MyTable2].[index_id] or [sys].[partitions].[index_id].
1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(9,92): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[index_id], [dbo].[MyTable2].[index_id] or [sys].[partitions].[index_id].
1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(7,46): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[OBJECT_ID], [dbo].[MyTable2].[OBJECT_ID] or [sys].[partitions].[OBJECT_ID].
1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(8,46): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[OBJECT_ID], [dbo].[MyTable2].[OBJECT_ID] or [sys].[partitions].[OBJECT_ID].
1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(9,46): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[OBJECT_ID], [dbo].[MyTable2].[OBJECT_ID] or [sys].[partitions].[OBJECT_ID].
1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(7,14): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[rows], [dbo].[MyTable2].[rows] or [sys].[partitions].[rows].
1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(8,14): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[rows], [dbo].[MyTable2].[rows] or [sys].[partitions].[rows].
1>C:\s\MyProject\db\MyProject.Database.Mssql\dbo\Views\myView.sql(9,14): ModelValidationError error SQL71501: SqlView: [dbo].[MyView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[MyTable1].[rows], [dbo].[MyTable2].[rows] or [sys].[partitions].[rows].
1>Found 12 error(s), skip building package
1>C:\Users\Ben.nuget\packages\msbuild.sdk.sqlproj\1.8.1\Sdk\Sdk.targets(193,5): error MSB3073: The command "dotnet "C:\Users\Ben.nuget\packages\msbuild.sdk.sqlproj\1.8.1\Sdk../tools/netcoreapp5.0/DacpacTool.dll" build -o "obj\Debug\netstandard2.0\MyProject.Database.Mssql.Build.dacpac" -n "MyProject.Database.Mssql.Build" -v "1.0.0" -sv SqlAzure -i "obj\Debug\netstandard2.0\MyProject.Database.Mssql.Build.InputFiles.txt" " exited with code 1.
1>Done building project "MyProjectDatabase.Mssql.Build.csproj" -- FAILED.
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========

[jeffrosenberg]

Yeah, looks like the package reference isn't being resolved properly. Here's what my master.nupkg looks like in my local NuGet repo, does yours look the same (other than names and version numbers)?

[BenjaminAbt]

Yes, the way to rename to master works. Thank you!!

2>Adding reference to C:\Users\Ben.nuget\packages\master\1.0.0/tools/master.dacpac with external parts master

To name the dacpac to 'MyProject.Database.MasterAzureSQLV12' does not work.

[jmezach]

@BenjaminAbt That's great news, glad to hear that works for you.

I am curious though why renaming the .dacpac file doesn't work. If we are going to publish the master.dacpac as a separate NuGet package I would rather not need to name that package master ;). Would you be willing to share a binary log (obtained using dotnet build /bl) with us so that we can figure out why that scenario is not working? Do note that the binary log includes all of the environment variables on your machine.

[BenjaminAbt]

I created a demo project to show you my errors but in this case it works.

I will check what are the differences why to use another name like "master" does not work in my real project.

[BenjaminAbt]

It works today in my projec too.
I guess it was a race condition of nuget caching... even if I think I have used restore with --force.

[jeffrosenberg]

@jmezach on the first question, I can see your point, but I see it a little differently -- you always get access to the API provided by the master database, particularly its functions and stored procedures. It's really a question of whether you need a three-part reference to its tables that determines whether you need a reference to it, and I see that as being much more similar to referencing another user database. To be honest, I don't have a really strong opinion here, I just think that consistency in how to accomplish a three-part reference to an object makes the most sense.

The bigger question, which we keep circling around, is whether we can redistribute master.dacpac in the first place, and I'm not sure how to answer that one. Personally, while I would like to do it, my gut feeling is that it's a no-no unless we get the okay from someone at MS. But I don't really think it's my call.

[BenjaminAbt]

But this (dependency to VS / VSBT) is in itself a requirement that you have with classic SQL projects anyway. So at least from this point of view this would not be a deterioration of the requirement at all.

[brettrowberry]

For a given version of SQL Server, does every instance of SQL Server have the exact same master database?

[jmezach]

@ErikEJ @jeffrosenberg I emailed @dzsquared about whether we can redistribute the .dacpacs. So far no response though.

[brettrowberry]

Here I thought Azure Data Studio was basically for querying:
https://docs.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-database-project-extension-build

https://docs.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-database-project-extension-build-from-command-line

[kevin-david]

... and those are actually available in their source code here:
https://github.com/microsoft/azuredatastudio/tree/main/extensions/sql-database-projects/BuildDirectory/SystemDacpacs

[dzsquared]

We don't have a solution for redistribution of the dacpacs, but they're significantly more available now with the addition of the functionality to Azure Data Studio.

[jmezach]

@ErikEJ Very nice, but I'm not sure if we want to depend on that during CI since that would require Azure Data Studio te be installed as well as the extension.

@dzsquared I'm wondering if we could add the repo linked by @kevin-david as a submodule here and then include them as part of the MSBuild.Sdk.SqlProj package, much like how we're including existing code from https://github.com/microsoft/sqltoolsservice/.

[pauldj54]

master.dacpac shipped with the DAC Framework

Hi @jmezach , @BenjaminAbt and @jeffrosenberg , @ErikEJ
First thanks for such a project. I am also fighting to create CI with a linux-based build server and struggeling but your project looks promising. However, I still have some issues in getting things working.
Basically, I took the master.dacpac shipped with Visual Studio 2019 for Azure DW.
I created 3 projects:

• The regular database project
• The database.build project
• A separated project to host the master.dacpac and package it

Solution layout looks like this:

I package my master project as a nuget package with the same PackageId for the dacpac in tools folder, in this case, master.

As you can see from the screenshot above the database.build project recognized the added reference.

When I tried to build the database.build project I got the following error:

Rebuild started...
1>------ Rebuild All started: Project: edl-synapse-dwh-databases.build, Configuration: Debug Any CPU ------
1>Using package name edl-synapse-dwh-databases.build and version 1.0.0
1>Using SQL Server version SqlAzure
1>Adding C:\Users\HernaPa\.nuget\packages\master\1.0.0\contentFiles\any\netstandard2.0\tools\master.dacpac to the model
1>Unhandled exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
1> ---> Microsoft.SqlServer.Dac.Model.DacModelException: Fehler beim Hinzufügen oder Aktualisieren von Objekten aufgrund folgender Ursachen:
1>EXEC : error SQL46010: Falsche Syntax in der Nähe von '?'.
1>
1>   at Microsoft.SqlServer.Dac.Model.SqlSchemaModelObjectService.ThrowIfModelErrorsExist(String errorMessage)
1>   at Microsoft.SqlServer.Dac.Model.SqlSchemaModelObjectService.DoAddOrUpdateObjects(String inputScript, String sourceName, TSqlObjectOptions options)
1>   at Microsoft.SqlServer.Dac.Model.SqlSchemaModelObjectService.AddOrUpdateObjects(String inputScript, String sourceName, TSqlObjectOptions options)
1>   at Microsoft.SqlServer.Dac.Model.TSqlModel.AddOrUpdateObjects(String inputScript, String sourceName, TSqlObjectOptions options)
1>   at MSBuild.Sdk.SqlProj.DacpacTool.PackageBuilder.AddInputFile(FileInfo inputFile) in /home/runner/work/MSBuild.Sdk.SqlProj/MSBuild.Sdk.SqlProj/src/DacpacTool/PackageBuilder.cs:line 85
1>   at MSBuild.Sdk.SqlProj.DacpacTool.Program.BuildDacpac(BuildOptions options) in /home/runner/work/MSBuild.Sdk.SqlProj/MSBuild.Sdk.SqlProj/src/DacpacTool/Program.cs:line 122
1>   --- End of inner exception stack trace ---
1>   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
1>   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
1>   at System.Delegate.DynamicInvokeImpl(Object[] args)
1>   at System.Delegate.DynamicInvoke(Object[] args)
1>   at System.CommandLine.Invocation.ModelBindingCommandHandler.InvokeAsync(InvocationContext context)
1>   at System.CommandLine.Invocation.InvocationPipeline.<>c__DisplayClass4_0.<<BuildInvocationChain>b__0>d.MoveNext()
1>--- End of stack trace from previous location ---
1>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseParseErrorReporting>b__21_0>d.MoveNext()
1>--- End of stack trace from previous location ---
1>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass16_0.<<UseHelp>b__0>d.MoveNext()
1>--- End of stack trace from previous location ---
1>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass25_0.<<UseVersionOption>b__0>d.MoveNext()
1>--- End of stack trace from previous location ---
1>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass23_0.<<UseTypoCorrections>b__0>d.MoveNext()
1>--- End of stack trace from previous location ---
1>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseSuggestDirective>b__22_0>d.MoveNext()
1>--- End of stack trace from previous location ---
1>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseParseDirective>b__20_0>d.MoveNext()
1>--- End of stack trace from previous location ---
1>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseDebugDirective>b__11_0>d.MoveNext()
1>--- End of stack trace from previous location ---
1>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<RegisterWithDotnetSuggest>b__10_0>d.MoveNext()
1>--- End of stack trace from previous location ---
1>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass14_0.<<UseExceptionHandler>b__0>d.MoveNext()
1>C:\Users\HernaPa\.nuget\packages\msbuild.sdk.sqlproj\1.11.4\Sdk\Sdk.targets(208,5): error MSB3073: The command "dotnet "C:\Users\HernaPa\.nuget\packages\msbuild.sdk.sqlproj\1.11.4\Sdk\../tools/netcoreapp5.0/DacpacTool.dll" build -o "obj\Debug\netstandard2.0\edl-synapse-dwh-databases.build.dacpac" -n "edl-synapse-dwh-databases.build" -v "1.0.0" -sv SqlAzure -i "obj\Debug\netstandard2.0\edl-synapse-dwh-databases.build.InputFiles.txt"       " exited with code 1.
1>Done building project "edl-synapse-dwh-databases.build.csproj" -- FAILED.

Sorry for the verbosity and some German text, basically the problem is, the DDLs of the master project are not compiling correctly: "EXEC : error SQL46010: Wrong Syntax near '?'."

My next try was to import this dacpac into a sql server database project like this:

As you can see in the following image, I am not able to compiled the imported master project, because the definition of the db objects is not correct, it seems like the schema is protected or to be honest, I cannot understand it:

Could you please provide me your opinion about it?

Any comment will be highly appreciated.

Best regards,
Paul

[jmezach]

@pauldj54 The stacktrace of the error you're getting seems to suggest that the error occurs while adding a .sql script to the model. But looking at the output it seems that it occurs at the time the reference to the .dacpac is made, which is weird. Could you share the contents of obj\Debug\netstandard2.0\edl-synapse-dwh-databases.build.InputFiles.txt?

It also looks like you're using Azure SQL Data Warehouse which I personally do not have any experience with. It does look like this is a separate SQL Server Version though, so perhaps it would work if you change <SqlServerVersion> to SqlDw in your project file?

[pauldj54]

SqlDw

Hi @jmezach ,

thanks for the fast response.
I changed the <SqlServerVersion> to SqlDw as proposed without success.

Here is the content of the edl-synapse-dwh-databases.build.InputFiles.txt file:

C:\Users\HernaPa\.nuget\packages\master\1.0.0\contentFiles\any\netstandard2.0\tools\master.dacpac
..\edl-synapse-dwh-databases\dbo\Tables\FactInternetSales.sql

I will try to create a sample project from scratch and see if I detect some differences.

[jmezach]

@pauldj54 Looking at that text file and the output you're getting again it looks like the .dacpac is being added as a regular .sql file to the model which obviously doesn't work. It should be added as a reference. Would you mind sharing the content of your project file?

[pauldj54]

@jmezach , yes, of course:

edl-synapse-dwh-databases.build.csproj (the build project):

<Project Sdk="MSBuild.Sdk.SqlProj/1.11.4">
	<PropertyGroup>
		<TargetFramework>netstandard2.0</TargetFramework>
		<SqlServerVersion>SqlDw</SqlServerVersion>
		<!-- For additional properties that can be set here,
        please refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#model-properties -->
	</PropertyGroup>

	<ItemGroup>
		<PackageReference Include="master" Version="1.0.0" DatabaseVariableLiteralValue="master" />
	</ItemGroup>
	
	<ItemGroup>
		<Content Include="..\edl-synapse-dwh-databases\**\FactInternetSales.sql" />
	</ItemGroup>
	
</Project>

master.csproj (the master.dacpac project):

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netstandard2.0</TargetFramework>
	<PackageId>master</PackageId>
	<Version>1.0.0</Version>
	<Authors>Paul Hernandez</Authors>
	<Company>skilled</Company>
	<GeneratePackageOnBuild>true</GeneratePackageOnBuild>
	<Description>SQL Server Master db schema to be referenced by other database projects</Description>
  </PropertyGroup>

  <ItemGroup>
	  <Content Include="tools\master.dacpac" />
  </ItemGroup>

</Project>

Finally, and I think it could be relevant, the master.1.0.0.nuspec:

<?xml version="1.0" encoding="utf-8"?>
<package xmlns="http://schemas.microsoft.com/packaging/2012/06/nuspec.xsd">
  <metadata>
    <id>master</id>
    <version>1.0.0</version>
    <authors>Paul Hernandez</authors>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <description>SQL Server Master db schema to be referenced by other database projects</description>
    <dependencies>
      <group targetFramework=".NETStandard2.0" />
    </dependencies>
    <contentFiles>
      <files include="any/netstandard2.0/tools/master.dacpac" buildAction="Content" />
    </contentFiles>
  </metadata>
  <files>
    <file src="C:\Users\HernaPa\source\repos\edl-synapse-dwh-db-project\edl-synapse-dwh-database-schemas\master\bin\Debug\netstandard2.0\master.dll" target="lib\netstandard2.0\master.dll" />
    <file src="C:\Users\HernaPa\source\repos\edl-synapse-dwh-db-project\edl-synapse-dwh-database-schemas\master\tools\master.dacpac" target="content\tools\master.dacpac" />
    <file src="C:\Users\HernaPa\source\repos\edl-synapse-dwh-db-project\edl-synapse-dwh-database-schemas\master\tools\master.dacpac" target="contentFiles\any\netstandard2.0\tools\master.dacpac" />
  </files>
</package>

I changed the absolute paths to the master.dacpac file but it gets overwritten when I build and package the project.

[jeffrosenberg]

@pauldj54 I think the issue here is in including the .dll file in master.1.0.0.nuspec. If you look at our example for packaging standalone projects in the README, you'll see the following, which doesn't include a .ddl file:

<?xml version="1.0" encoding="utf-8" ?>
<package xmlns="http://schemas.microsoft.com/packaging/2011/10/nuspec.xsd">
  <metadata>
    <id>your-dacpac-name</id>
    <version>your-version-number</version>
    <description>your-description</description>
    <authors>your-author</authors>
    <owners>your-owner</owners>
    <packageTypes>
      <packageType name="dacpac" />
    </packageTypes>
  </metadata>
  <files>
    <file src="fileName.dacpac" target="tools/" />
  </files>
</package>

Try removing that and give it a whirl!

[jmezach]

I think the problem is that the master.dacpac is not in the tools folder, which is where MSBuild.Sdk.SqlProj expects it to be when you add a <PackageReference> to a NuGet package. How are you creating the NuGet package? Using dotnet pack or nuget pack? If you're using dotnet pack I don't think the .nuspec is even being used.

I guess you could try the approach described in #64 (comment) with nuget pack, or you could remove the .nuspec file altogether and change the <ItemGroup> to something like this:

<ItemGroup>
  <None Include="tools\master.dacpac" Pack="True" PackagePath="tools" />
</ItemGroup>

[pauldj54]

Hi @jmezach ,
I applied what you have documented in the readme and it worked:

• Create a package using the standalone master.dacpac
• Used nuget pack instead of dotnet pack
• Referenced it in the database.build project
• Clean up nuget cash and delete old references, since the database.build project was still expecting the master.dll file

This worked locally in my console.
My next challenge is to get this work in an azure pipeline running in a linux-based agent.

[ErikEJ]

Yes, there are multiple examples of similar unofficial packages with SQL Server related libraries.

[orloffm]

Gentlemen, but can you please support a local .dacpac file? For us mere mortals in a Windows corporate banking environment the idea of publishing nuget somewhere is terrifying. Would be so much easier just to put the file into the project. For example, to tools/master.dacpac.. Thanks.

[jeffrosenberg]

@orloffm We rely on NuGet to resolve package references, so I'm not sure it makes sense to write our own methods of handling a local .dacpac file, although I'll defer to @jmezach who may disagree with me. In the meantime, I can think of a few ways to handle this that are already supported:

1. You can reference a local project file (.csproj) without packaging it into a dacpac
2. You can use an internal corporate NuGet repository if one exists, rather than publishing to nuget.org -- that's what my company does
3. You can distribute the .nupkg file in whatever way you want -- a shared file server, for example -- and have people use nuget push to add it to their local NuGet repository
4. You can distribute the .dacpac file in whatever way you want, and have people use nuget pack to package it and nuget push to add it to their local NuGet repository

[jeffrosenberg]

@jmezach I took a look at the license file for the Azure Data Studio repository, and it doesn't lead me to think they would be amenable to our using it as a submodule:

Microsoft Corporation ("Microsoft") grants you a nonexclusive, perpetual,
royalty-free right to use, copy, and modify the software code provided by us
("Software Code"). You may not sublicense the Software Code or any use of it
(except to your affiliates and to vendors to perform work on your behalf)
through distribution, network access, service agreement, lease, rental, or
otherwise. Unless applicable law gives you more rights, Microsoft reserves all
other rights not expressly granted herein, whether by implication, estoppel or
otherwise.

[jmezach]

@jeffrosenberg I'm not entirely sure what you're trying to say. Do you mean that the license isn't a blocker for us to use as a submodule, or do you think it is?

[jeffrosenberg]

@jmezach sorry if I was unclear. I think that license is a blocker, since it says "You may not sublicense the Software Code or any use of it."

[philipobriensgs]

@pauldj54 Hi Paul, did you have any luck with Azure?

I pushed master.dacpac in a nuget package to an internal repo, building locally works.

I can build the project in Azure but when I try to deploy the dacpac inside a Docker container I get:

I have tried changing the MSBuild.Sdk.SqlProj code to suppress dependency errors but to no avail:

@jeffrosenberg @jmezach Does anyone have any ideas?

[jmezach]

@philipobriensgs I believe that we copy any.dacpacreferenced by your project (regardless of whether it is a PackageReference or a ProjectReference) to the output folder, so they should be available there. You'll need those in order to deploy your .dacpac. How are you deploying it in the Docker container?

[philipobriensgs]

@jmezach I'm using a dockefile which builds an Unbuntu container, all the required dacpac's get copied to the /tmp folder and then I call sqlpackage /a:Publish.

I think sqlpackage doesn't like how the two Nuget added packages are referenced.
C:\Users\philip_obrien.nuget\packages\sgs.master.dacpac\1.0.0**/tools/SGS.Master.dacpac.dacpac**
C:\Users\philip_obrien.nuget\packages\sgs.tsqlt.dacpac\1.0.0**/tools/SGS.tSQLt.dacpac.dacpac**

The required referenced dacpac's are in the same folder as the dacpac I am trying to publish. I have tried copying the references to /tools but that didn't work either.

[philipobriensgs]

@jmezach I managed to get past the referenced dacpac's not found error by building the dacpac using the -r option and correcting the forward slashes to be a back slashes,
-r "C:\Users\philip_obrien.nuget\packages\sgstsqltdacpac\1.0.0\tools\SGStSQLtDacpac.dacpac;"

sqlpackage now finds the referenced dacpac's.

The sqlproj I'm building has a reference to tSQLtCLR.dll, the container now complains that about missing dll's, this is probably a separate issue.

[jmezach]

That seems to suggest that this is a platform issue. Are you building the .dacpac on the same platform as the platform you're deploying to?

[philipobriensgs]

I'm building locally on Windows and then deploying locally to a Docker Ubuntu container.
Before I added MSBuild.Sdk.SqlProj I was able to get the Windows built dacpac's deploy to the Ubuntu container.

I'm using MSBuild.Sdk.SqlProj so I can build on Ubuntu build agents in Azure. I think you are correct though, these do seem like platform issues.

[Sirozha1337]

Is there a way of ignoring those ModelValidation errors for procedures which use references to master database? Integrating master dacpac seems like too much of hustle.

[jmezach]

You might be able to use the SuppressTSqlWarnings support we've added in 1.12.0 although I haven't actually tested this ;).

[Sirozha1337]

You might be able to use the SuppressTSqlWarnings support we've added in 1.12.0 although I haven't actually tested this ;).

Doesn't seem to work. Probably because it's not a warning:
error SQL71501: SqlView: [tSQLt].[Tests] has an unresolved reference to object [sys].[procedures]

Found another solution for my case. I had a problem with SQL Database project which uses tSQLt for Unit Testing.

Here it is, in case someone stumbles upon it with the same issue.

1. Add PreDeploymentScript.sql with this content:

:r PrepareServer.sql
:r tSQLt.class.sql

2. PrepareServer.sql and tSQLt.class.sql are the files distributed in tSQLt package, they should be put in the same directory as PreDeploymentScript.sql.
3. Reference it in project file Database.Test.Build.csproj:
   <PreDeploy Include="..\Database.Test\PreDeployment\PreDeploymentScript.sql" />
4. Remove references to other tSQLt files (schema, procedures, etc).
   It will throw warnings about tSQLt being unresolved for each of your TestCase procedures:
   warning SQL71502: SqlProcedure: [TestSuite1].[test positive] has an unresolved reference to object [tSQLt].[AssertEquals]
   Which you can actually remove using the option suggested above.
5. Even though there are warnings, the dacpac will be built correctly and after deploying it, you will be able to run your tSQLt tests without a hitch.

[pinkfloydx33]

@jmezach I managed to get past the referenced dacpac's not found error by building the dacpac using the -r option and correcting the forward slashes to be a back slashes,
-r "C:\Users\philip_obrien.nuget\packages\sgstsqltdacpac\1.0.0\tools\SGStSQLtDacpac.dacpac;"

@philipobriensgs Can you explain what you did? Where/to what did you pass the -r option?
I'm having this issue with building on windows and deploying to Linux and I cannot seem to get passed sql package not finding the references. Any guidance you have would be appreciated

[philipobriensgs]

@pinkfloydx33 If I remember correctly I was calling the DacpacTool.exe directly and passing the -r option in there. The -r option is available inside MsBuild.Sdk.SqlProj.Dacpactool.Program.cs

I ended up building on Linux and everything is working now.

[jmezach]

So if I understand correctly SqlPackage.exe chokes on back-slashes for references, but only on non-Windows platforms, while forward slashes work on all platforms? I guess we could easily change this here by normalizing the path to always have forward slashes.

[pinkfloydx33]

@jmezach Yes, that seems to be correct.

• If the package is built on Linux, the deployment works on both Windows and Linux
• If the package is built on Windows, the deployment:
  • only works on Windows by default (using SDK directly)
  • works on Linux if you normalized all back-slashes to forward-slashes (invoking the tool directly and passing -r with normalized paths)

[asbjornb]

I also can't seem to get this to work. Think I tried everything mentioned in the three open issues on master.dacpac. I now put a minimal example up on github so hopefully someone can see what I'm doing wrong: Example at my github

In the example I made a minimal project, Packed a nuget package with the master.dacpac included in tools/, referenced it and made 2 small views with dependency on the models in master.dacpac.
I tried every which way with backslashes and forward slashes when packing the nuget in case that matters:
<file src=".\tools\master.dacpac" target="tools" /> <file src="./tools/master.dacpac" target="tools/" /> <file src="./tools/master.dacpac" target="./tools/" />
and many more. None of them got my project to build but they all had the master.dacpac in a tools folder in my cached nuget package:

When I build I get missing reference errors but also one error that suggests a backslash/forward slash mixup:

Any ideas?

[jmezach]

@asbjornb Can you share the output of running dotnet build on this project? It should show whether or not the reference is being picked up or not.

[asbjornb]

Here it is:

PS C:\code\dacex> dotnet build Databases.Build.csproj
MSBuild version 17.3.2+561848881 for .NET
  Determining projects to restore...
  All projects are up-to-date for restore.
  Using target framework net6.0 to run DacpacTool
  Using package name Databases.Build and version 1.0.0
  Using SQL Server version Sql140
  Adding C:\code\dacex\test.sql to the model
  Adding C:\code\dacex\test2.sql to the model
C:\code\dacex\test.sql(3,8): error SQL71561: SqlView: [dbo].[test] has an unresolved reference to object [master].[sys]
.[all_objects].[object_id]. [C:\code\dacex\Databases.Build.csproj]
C:\code\dacex\test.sql(4,8): error SQL71561: SqlView: [dbo].[test] has an unresolved reference to object [master].[sys]
.[all_objects]. [C:\code\dacex\Databases.Build.csproj]
C:\code\dacex\test.sql(2,20): error SQL71561: SqlView: [dbo].[test] has an unresolved reference to object [master].[sys
].[all_objects].[name]. [C:\code\dacex\Databases.Build.csproj]
C:\code\dacex\test2.sql(2,50): error SQL71501: SqlView: [dbo].[test2] contains an unresolved reference to an object. Ei
ther the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [s
ys].[sql_expression_dependencies].[referenced_entity_name] or [sys].[sql_expression_dependencies].[se]::[referenced_ent
ity_name]. [C:\code\dacex\Databases.Build.csproj]
C:\code\dacex\test2.sql(3,10): error SQL71501: SqlView: [dbo].[test2] has an unresolved reference to object [sys].[sql_
expression_dependencies]. [C:\code\dacex\Databases.Build.csproj]
C:\code\dacex\test2.sql(2,20): error SQL71501: SqlView: [dbo].[test2] contains an unresolved reference to an object. Ei
ther the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [s
ys].[sql_expression_dependencies].[referenced_schema_name] or [sys].[sql_expression_dependencies].[se]::[referenced_sch
ema_name]. [C:\code\dacex\Databases.Build.csproj]
C:\code\dacex\test.sql(2,20): error SQL71561: SqlComputedColumn: [dbo].[test].[name] has an unresolved reference to obj
ect [master].[sys].[all_objects].[name]. [C:\code\dacex\Databases.Build.csproj]
C:\code\dacex\test.sql(3,8): error SQL71561: SqlComputedColumn: [dbo].[test].[object_id] has an unresolved reference to
 object [master].[sys].[all_objects].[object_id]. [C:\code\dacex\Databases.Build.csproj]
  Found 8 error(s), skip building package
C:\Users\asb\.nuget\packages\msbuild.sdk.sqlproj\2.1.0\Sdk\Sdk.targets(238,5): error MSB3073: The command "dotnet "C:\U
sers\asb\.nuget\packages\msbuild.sdk.sqlproj\2.1.0\Sdk\../tools/net6.0/DacpacTool.dll" build -o "obj\Debug\netstandard2
.0\Databases.Build.dacpac" -n "Databases.Build" -v "1.0.0" -sv Sql140 -i "obj\Debug\netstandard2.0\Databases.Build.Inpu
tFiles.txt"    -dp IncludeCompositeObjects=true         " exited with code 1. [C:\code\dacex\Databases.Build.csproj]

Build FAILED.

C:\code\dacex\test.sql(3,8): error SQL71561: SqlView: [dbo].[test] has an unresolved reference to object [master].[sys]
.[all_objects].[object_id]. [C:\code\dacex\Databases.Build.csproj]
C:\code\dacex\test.sql(4,8): error SQL71561: SqlView: [dbo].[test] has an unresolved reference to object [master].[sys]
.[all_objects]. [C:\code\dacex\Databases.Build.csproj]
C:\code\dacex\test.sql(2,20): error SQL71561: SqlView: [dbo].[test] has an unresolved reference to object [master].[sys
].[all_objects].[name]. [C:\code\dacex\Databases.Build.csproj]
C:\code\dacex\test2.sql(2,50): error SQL71501: SqlView: [dbo].[test2] contains an unresolved reference to an object. Ei
ther the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [s
ys].[sql_expression_dependencies].[referenced_entity_name] or [sys].[sql_expression_dependencies].[se]::[referenced_ent
ity_name]. [C:\code\dacex\Databases.Build.csproj]
C:\code\dacex\test2.sql(3,10): error SQL71501: SqlView: [dbo].[test2] has an unresolved reference to object [sys].[sql_
expression_dependencies]. [C:\code\dacex\Databases.Build.csproj]
C:\code\dacex\test2.sql(2,20): error SQL71501: SqlView: [dbo].[test2] contains an unresolved reference to an object. Ei
ther the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [s
ys].[sql_expression_dependencies].[referenced_schema_name] or [sys].[sql_expression_dependencies].[se]::[referenced_sch
ema_name]. [C:\code\dacex\Databases.Build.csproj]
C:\code\dacex\test.sql(2,20): error SQL71561: SqlComputedColumn: [dbo].[test].[name] has an unresolved reference to obj
ect [master].[sys].[all_objects].[name]. [C:\code\dacex\Databases.Build.csproj]
C:\code\dacex\test.sql(3,8): error SQL71561: SqlComputedColumn: [dbo].[test].[object_id] has an unresolved reference to
 object [master].[sys].[all_objects].[object_id]. [C:\code\dacex\Databases.Build.csproj]
C:\Users\asb\.nuget\packages\msbuild.sdk.sqlproj\2.1.0\Sdk\Sdk.targets(238,5): error MSB3073: The command "dotnet "C:\U
sers\asb\.nuget\packages\msbuild.sdk.sqlproj\2.1.0\Sdk\../tools/net6.0/DacpacTool.dll" build -o "obj\Debug\netstandard2
.0\Databases.Build.dacpac" -n "Databases.Build" -v "1.0.0" -sv Sql140 -i "obj\Debug\netstandard2.0\Databases.Build.Inpu
tFiles.txt"    -dp IncludeCompositeObjects=true         " exited with code 1. [C:\code\dacex\Databases.Build.csproj]
    0 Warning(s)
    9 Error(s)

Time Elapsed 00:00:02.70

It would seem it does not get picked up. Any ideas on that? I can see the package as installed in "Manage nuget packages" menu in VS and it also gets cached in my local nuget folder.

[asbjornb]

Thanks! That worked right away.

I heard on some Microsoft backchannel that they are working on these issues - both on nuget packages for system databases and on linux build pipeline support. I can't vouch for credibility but hopefully there will be more official support at some point.

[jmezach]

@dzsquared Thank you very much for making this happen. I believe this will greatly benefit our users as well.

That being said I think we'll have to make a few changes to support this, as we're currently assuming that the package ID and the .dacpac contained within it share the same name, which is not the case here. Shouldn't be too hard to fix that though.

[jmezach]

I'm glad to report that we have just released a new version of MSBuild.Sdk.SqlProj (version 2.5.0) with support for referencing the packages mentioned by @dzsquared. We have a dedicated section in our README now that describes how to reference the master database. Thanks to everyone who made this happen!