dotnet build should either build or ignore sqlproj SQL project - not error out

[SidShetye]

Steps to reproduce

1. Have a visual studio solution with two projects, one asp.net core project (*.csproj) and the other a SQL project (*.sqlproj).
2. Issue dotnet build at solution level

Expected behavior

Ideal

The solution is successfully built by either msbuild or natively by the dotnet cli (or a plugin).

Minimum Acceptable

Solution is built by skipping projects (e.g. sqlproj) that dotnet CLI cannot handle

Actual behavior

Solution build fails with error MSB4019: The imported project "C:\Program Files\dotnet\sdk\3.0.100-preview8-013656\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found

Previous reports

This was previously reported here but was prematurely closed without fixing the bug and subsequently ignored.

Environment data

Click to expand

dotnet --info output:

.NET Core SDK (reflecting any global.json):
 Version:   3.0.100-preview8-013656
 Commit:    8bf06ffc8d

Runtime Environment:
 OS Name:     Windows
 OS Version:  10.0.18362
 OS Platform: Windows
 RID:         win10-x64
 Base Path:   C:\Program Files\dotnet\sdk\3.0.100-preview8-013656\

Host (useful for support):
  Version: 3.0.0-preview8-28405-07
  Commit:  d01b2fb7bc

.NET Core SDKs installed:
  2.1.202 [C:\Program Files\dotnet\sdk]
  2.1.302 [C:\Program Files\dotnet\sdk]
  2.1.400 [C:\Program Files\dotnet\sdk]
  2.1.401 [C:\Program Files\dotnet\sdk]
  2.1.402 [C:\Program Files\dotnet\sdk]
  2.1.403 [C:\Program Files\dotnet\sdk]
  2.1.502 [C:\Program Files\dotnet\sdk]
  2.1.503 [C:\Program Files\dotnet\sdk]
  2.1.504 [C:\Program Files\dotnet\sdk]
  2.1.505 [C:\Program Files\dotnet\sdk]
  2.1.507 [C:\Program Files\dotnet\sdk]
  2.1.700 [C:\Program Files\dotnet\sdk]
  2.1.701 [C:\Program Files\dotnet\sdk]
  2.1.801 [C:\Program Files\dotnet\sdk]
  2.2.300 [C:\Program Files\dotnet\sdk]
  2.2.301 [C:\Program Files\dotnet\sdk]
  2.2.401 [C:\Program Files\dotnet\sdk]
  3.0.100-preview8-013656 [C:\Program Files\dotnet\sdk]

.NET Core runtimes installed:
  Microsoft.AspNetCore.All 2.1.2 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.1.4 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.1.5 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.1.6 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.1.7 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.1.8 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.1.9 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.1.11 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.1.12 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.2.5 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.2.6 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.App 2.1.2 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.1.4 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.1.5 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.1.6 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.1.7 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.1.8 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.1.9 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.1.11 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.1.12 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.2.5 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.2.6 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 3.0.0-preview8.19405.7 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.NETCore.App 2.0.9 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.2 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.3-servicing-26724-03 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.4 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.5 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.6 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.7 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.8 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.9 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.11 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.1.12 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.2.5 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.2.6 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 3.0.0-preview8-28405-07 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.WindowsDesktop.App 3.0.0-preview8-28405-07 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]

To install additional .NET Core runtimes or SDKs:
  https://aka.ms/dotnet-download

[livarcocc]

This is by design.

The CLI does not try to reason about the set of projects it supports natively because that list of not final.

User's can customize their builds to make different project types work. And this has happened before.

Our recommendation here is that you either use full framework MSBuild.

[SidShetye]

Why doesn't the CLI figure out what projects it can work with? If Microsoft's own tool team can't figure out that the supported projects are for a given release/config, who else is in a better position?

As an example, CI scripts are far simpler when relying just on dotnet and a single way of doing things versus having somethings in msbuild and other in dotnet.

Can you elaborate why this is a design choice? It seems like a very poor choice leading to poor customer experience. The tools are supposed to work for us, making us more productive - not the other way around. Without reasonable dialog, this just seems very lazy (on part of the tool ;) !)

[livarcocc]

Because of the open ended nature of MSBuild, verifying if a project is buildable would pretty much require a build to happen, for errors on it to be ignored (how exactly would we discern actual build errors from errors related to the project not being supported) and then a second build would have to happen with some projects excluded.

This would be extremely costly and every build would have to pay that cost.

We need the evaluation because we don't know which projects are not supported. We have a positive list of projects we support natively, but nothing prevents customers to write their own props/targets to support different project types not originally supported by the CLI. The list is open-ended.

[SidShetye]

I see. What about shipping a default C:\Program Files\dotnet\sdk\3.0.100-preview8-013656\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets that's a "null target" that's defined as "nothing to do here" or "everything already done", effectively skipping it?

This could patch out certain known problematic project types till either they are actually supported by the MSFT tools group or a customer replaces that with a custom/working one? On surface it appears to be simple fix and would be "out of the box" friendly.

[livarcocc]

I don't think patching these 1:1 to noop is the right approach here. The number of project types is open ended. Why do one and not the others? Plus, I find the experience of silently ignoring a project type to be very dangerous.

Folks may interpret that as success while noop is very far from that.

[another-guy]

@livarcocc a pretty obvious option is to offer an explicit CLI command key.

dotnet build --unsupportedProjectAction=<error|warning|ignore>, with error being a default value for compatibility reasons.

---

As far as I understand, as of now, there's no even a workaround, right? The only way to have dotnet succeed would be to remove the Database project from the solution built via dotnet.

[tunminster]

As far as i know, that can be ignored from the target project.

[SidShetye]

As far as i know, that can be ignored from the target project.

How do you ignore the SQL project?

[NicoKno]

As far as i know, that can be ignored from the target project.

How do you ignore the SQL project?

Rightclick on solution in Visual Studio and goto "Configuration Manager". Remove the project from build configuration accordingly.

In my case, I left it activated for "Debug" config and removed "build" and "deploy" for "Release" config.
Our buildjob builds via
dotnet build --configuration "Release"
therefore the solution build now ignores the sqlproj-file.

[ghost]

Will SQL projects ever be supported in .NET Core? If not then are they officially deprecated and is there an alternative? (Other than EF)

[funktionalsystems]

Will SQL projects ever be supported in .NET Core? If not then are they officially deprecated and is there an alternative? (Other than EF)

I discovered this alternative, which I've been extremely happy with: https://github.com/rr-wfm/MSBuild.Sdk.SqlProj

[mwwhited]

People aren't asking you to make a one off patch. They are asking you to support your own technology that we have relied on for years.

The SDK project above is somewhat compelling but it doesn't support the tooling around SQL Server projects such as Schema Compare, DataCompare and SQLCLR (which that too would be nice to get upgraded to dotnet core).

[blueiced56]

I discovered this alternative, which I've been extremely happy with: https://github.com/rr-wfm/MSBuild.Sdk.SqlProj

It looks promising. However it would be nice if this project would have the functionality of the original SQL Server template. It would make things a lot easier for database developers.

[hartmark]

+1 I like this to be re-opened and solved in a more long-term way than to refer to the msbuild-tooling

[RyanThomas73]

Can anyone provide me with a snippet or documentation link on how I can detect within a project file when msbuild is running under the dotnet cli and therefore the SSDT extensions are not available and when it is running the 'full framework msbuild' and the SSDT extensions are available?

e.g.

<Project Sdk="Microsoft.NET.Sdk">
     <PropertyGroup>
         <IsDotnetCliBuild Condition="...">true</IsDotnetCliBuild>
         <IsFullMsbuild Condition="...">true<IsFullMsbuild >
     </PropertyGroup>
</Project>

[KalleOlaviNiemitalo]

@RyanThomas73, MSBuildRuntimeType in MSBuild reserved and well-known properties is "Core" in dotnet build.

[holytshirt]

Some progress has been made, preview has been released
https://github.com/microsoft/DacFx/tree/main/src/Microsoft.Build.Sql

<Sdk Name="Microsoft.Build.Sql" Version="0.1.3-preview" />

[commonsensesoftware]

For anyone else coming across this issue, it's worth noting that you can build from the .NET CLI if, and only if, Visual Studio is also installed. CI builds often build via the .NET CLI, which can still be supported as Visual Studio is also present. Visual Studio and the .NET SDK use different copies of MSBuild, which is why it doesn't intrinsically work. This is useful and interesting when you want to use the CLI, such dotnet build, even though you have Visual Studio installed. I have found that common in CI builds in the past.

To achieve this, you'll need a custom targets file that looks like this:

<Project>

  <PropertyGroup>
    <BuildDependsOn>ResolveMSBuild;$(BuildDependsOn)</BuildDependsOn>
    <PublishDependsOn>ResolveMSBuild;$(PublishDependsOn)</PublishDependsOn>
  </PropertyGroup>

  <!-- resolve the version of msbuild that ships with visual studio so we can recursively call ourself from dotnet.exe -->
  <Target Name="ResolveMSBuild" Condition=" '$(_MSBuildExe)' == '' ">
    
    <PropertyGroup>
      <VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">17.0</VisualStudioVersion>
      <VsWhere>$(MSBuildProgramFiles32)\Microsoft Visual Studio\Installer\vswhere.exe</VsWhere>
    </PropertyGroup>
    
    <!-- determine where vs is installed using vswhere.exe -->
    <Exec Command="&quot;$(VsWhere)&quot; -latest -format value -property installationPath" ConsoleToMsBuild="true">
      <Output TaskParameter="ConsoleOutput" PropertyName="VsInstallDir" />
    </Exec>
    
    <PropertyGroup>
      <_MSBuildExe>$([MSBuild]::NormalizePath($(VsInstallDir)\MSBuild\Current\Bin\MSBuild.exe))</_MSBuildExe>
    </PropertyGroup>

  </Target>

  <!-- override the default targets if building with 'dotnet build' -->
  <Target Name="Restore" />
  <Target Name="VSTest" />
  <Target Name="Build" DependsOnTargets="$(BuildDependsOn)">
    <Message Text="$(MSBuildProjectName) -> $(BuildScriptName)" Importance="high" />
    <Exec Command="&quot;$(_MSBuildExe)&quot; &quot;$(MSBuildProjectFullPath)&quot; -t:Build -p:Configuration=$(Configuration) -v:m -nologo" />
  </Target>
  <Target Name="Publish" DependsOnTargets="$(PublishDependsOn)">
    <Exec Command="&quot;$(_MSBuildExe)&quot; &quot;$(MSBuildProjectFullPath)&quot; -t:Publish -p:Configuration=$(Configuration) -p:SqlPublishProfilePath=$(SqlPublishProfilePath) -v:m -nologo" />
  </Target>

</Project>

You can name it whatever you want and place it wherever you want. Let's say we called it dotnet.override.targets. Now, you just need to update your *.sqlproj with the following:

<!-- only override the default targets when running via dotnet.exe -->
<Import Project="dotnet.override.targets" Condition=" '$(MSBuildRuntimeType)' == 'Core' " />

Assuming that Visual Studio is found on your box, now you can continued to build from the VS IDE or you can build from the command line with dotnet build or dotnet publish. This technique doesn't solve the problem of building on Linux, but it does solve mixing and matching building in the IDE vs CLI. You could make a few other tweaks to add additional validation, etc.