Unable to build in VS - Visual Studio support for Microsoft.Build.Sql

[ErikEJ]

• SqlPackage or DacFx Version: 161
• .NET Framework (Windows-only) or .NET Core: Windows
• Environment (local platform and source/target platforms):

Steps to Reproduce:

1. Attempt to load/build a new sqlproj based on template 0.1.7
2. Load fails
3. adding this enables the build:

    <TargetDatabaseSet>True</TargetDatabaseSet>
    <TargetFrameworkVersion>v4.8</TargetFrameworkVersion>
    <TargetFrameworkProfile />
    <ProjectGuid>{f1ee546e-2489-4a7b-9d69-7e2849143721}</ProjectGuid>

[Real-JD-UK]

@ErikEJ can you even build the project in VS?

even with the 4 lines you mentioned, I get "Error: : Your project does not reference ".NETFramework,Version=v4.8" framework. Add a reference to ".NETFramework,Version=v4.8" in the "TargetFrameworks" property of your project file and then re-run NuGet restore."

full project contents:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="0.1.3-preview" />
  <PropertyGroup>
    <Name>My.Database</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <TargetDatabaseSet>True</TargetDatabaseSet>
    <TargetFrameworkVersion>v4.8</TargetFrameworkVersion>
    <TargetFrameworkProfile />
    <ProjectGuid>{0e4d9e11-0dd4-4f05-9caa-684d32f16f99}</ProjectGuid>
  </PropertyGroup>
</Project>

[ErikEJ]

@Real-JD-UK you are not using the latest SDK version

[ssreerama]

Hi @ErikEJ , can you be more specific on how you create the project and where you are trying to build it?

I have created a sample SDK style project on Azure Data Studio (1.40.0) and was able to build it in ADS. Are you trying to open the project in the Visual Studio? If so, the SDK style project is not supported in VS yet! (Edited: typo - isn't supported)

With the project I was able to build "dotnet build "path/sampleproj.sqlproj" /p:NetCoreBuild=true", but msbuild is failing with "our project does not reference ".NETFramework,Version=v4.8" framework"! is this what you are referring about?

[ErikEJ]

Yes, as the title of the issue states this is about building in VS. Its all fine that you can build in limited cross platform tooling but the circle is not complete until you can build in VS and get the intellisense and table editing experience.

[ssreerama]

I had a typo in my previous comment and edited. We do have a future enhancement for the VS to support SDK style projects and will support that in future.

[dzsquared]

Support for Microsoft.Build.Sql sqlprojects is planned for future Visual Studio (SSDT) releases. Reopening this item until it is shipped

[CSharpFiasco]

I had a similar issue, and I was able to resolve this by deleting the obj folder for my project. And so, I set up the project to clean the project and to delete the obj folder before clean. It's still a work in progress, but I landed on the code below to both build in Visual Studio 2022 and via the dotnet cli. The dotnet cli seems to build just fine without deleting the obj folder, but it wasn't deleting the folder, so I just added ContinueOnError="True".

Edited code since Visual Studio 2022 didn't seem to output the dacpac.

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" ToolsVersion="4.0">
  <Sdk Name="Microsoft.Build.Sql" Version="0.1.7-preview" />
  <PropertyGroup>
    <Name>sample</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <ProjectGuid>{939b2fc6-e398-4920-a5d4-7b3816c0b3a5}</ProjectGuid>
    <TargetDatabaseSet>True</TargetDatabaseSet>
  </PropertyGroup>
  <ItemGroup>
    <Folder Include="Properties" />
  </ItemGroup>
  <Target Name="PreBuild" BeforeTargets="Build">
    <Message Text="PreBuild - Starting to remove obj folder" />
    <!-- Remove obj folder -->
    <RemoveDir Directories="$(BaseIntermediateOutputPath)" Condition="Exists('$(BaseIntermediateOutputPath)')" ContinueOnError="True" />
    <Message Text="PreBuild - Finished removing obj folder" />
  </Target>
</Project>

[nholland20]

For those with Visual Studio issues? Are your files loading properly in Visual Studio? Also, if I have a .sql file in my output directory (which I am setting as a custom path in the project file, e.g. <OutputPath>.\sql</OutputPath>), it won't load the project. Has anyone seen this issue?

[Nathalilly]

When I try to build in visual studio after building in AzureDataSudio I get this error: "Error: : Your project does not reference ".NETFramework,Version=v4.8.1" framework. Add a reference to ".NETFramework,Version=v4.8" in the "TargetFrameworks" property of your project file and then re-run NuGet restore."

If I delete the project.assets.json file from the obj folder then it will build fine.

Found there is this already: <Target Name="BeforeBuild"> <Delete Files="$(BaseIntermediateOutputPath)\project.assets.json" /> </Target>

But it doesn't work for me had to switch to: <Target Name="PreBuild" BeforeTargets="PreBuildEvent"> <Delete Files="$(SolutionDir)$(ProjectName)\obj\project.assets.json" /> </Target>

I used just $(ProjectDir) to start with - this builds in visual studio but failed more often than not in Azure Data Studio with "C:\Program Files\dotnet\sdk\7.0.400\Sdks\NuGet.Build.Tasks.Pack\build\NuGet.Build.Tasks.Pack.targets(321,5): error MSB4018: The "GetProjectReferencesFromAssetsFileTask" task failed unexpectedly. "

Using the SolutionDir instead makes the path *Undefined* in AzureDataStudio

[gzinger]

When will have support for SDK-style projects in Visual Studio? It seems like an easy-to-do feature that would be very useful for many of us, but seems to be delayed for more than a year already.

[nholland20]

With the new version, "0.1.15-preview", I can no longer build from the command line if I have the fix that CSharpFiasco posted above to build from Visual Studio. However, if I don't have that fix, I still can't build from Visual Studio. The error: \Microsoft.PackageDependencyResolution.targets(267,5): error NETSDK1004: Assets file '<filepath>\project.assets.json' not found. Run a NuGet package restore to generate this file. Running dotnet restore did not work. Only deleting the PreBuild target made it work.

[RyanThomas73]

Sharing what my customized .sqproj file ended up being for others who come across this thread while trying to troubleshoot new use of this project sdk:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="0.1.15-preview" />
  <PropertyGroup>
    <Name>YourProjectName</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <ProjectGuid>{your-project-guild}</ProjectGuid>
    <TargetDatabaseSet>True</TargetDatabaseSet>
    <!-- 
      The sdk will target netstandard2.1 automatically when doing a dotnet cli build but we need to target 
      netframework when the msbuild type is full (a.k.a. building from within Visual Studio) until the Visual Studio
      SSDT extension supports the new sql project SDK.
    -->
    <TargetFrameworkVersion Condition="'$(MSBuildRuntimeType)' != 'Core'">v4.8</TargetFrameworkVersion>
    <TargetFrameworkProfile Condition="'$(MSBuildRuntimeType)' != 'Core'" />
  </PropertyGroup>

  <!-- 
    NOTE: The sql project sdk automatically includes a default globbing pattern for sql files but we still need them to be listed 
    explicitly for VisualStudio SSDT to display them

    See: https://github.com/microsoft/DacFx/blob/main/src/Microsoft.Build.Sql/docs/Converting-Existing.md#default-sql-files-included-in-build
    See: https://github.com/microsoft/DacFx/blob/a888164f1e83f0b64f130875600d90205adb7c23/src/Microsoft.Build.Sql/sdk/Sdk.props#L35
  -->
  <ItemGroup>
    <Build Include="**/*.sql" />
    <Build Remove="bin/**/*.sql" />
    <Build Remove="obj/**/*.sql" />
    <!-- Also Build Remove any .sql file patterns you want to exclude   e.g. -->
    <Build Remove="StaticDataScriptsReferencedInPostDeploy/**/*.sql" />

    <!-- Explicitly include your folders so that VisualStudio SSDT will display your files -->
    <Folder Include="OtherFolder" />
    <Folder Include="StaticDataScriptsReferencedInPostDeploy" />
    <Folder Include="Tables" />

    <None Include="README.md" />
    <None Include="StaticDataScriptsReferencedInPostDeploy/**/*.sql" />
  </ItemGroup>

  <ItemGroup>
    <PostDeploy Include="Script.PostDeployment.sql" />
  </ItemGroup>

  <!-- 
    Building through visual studio (targeting net48) will fail if a previous build through the dotnet core cli
    has run and created the obj/project.assets.json file. This custom target should delete
    the file before the visual studio build starts.
  -->
  <Target Name="PreBuild" BeforeTargets="PreBuildEvent" Condition="'$(MSBuildRuntimeType)' != 'Core'">
    <Message Importance="high" Text="Ensuring the $(MSBuildThisFileDirectory)\obj\project.assets.json file is removed, if necessary, so that the database project can be built through VisualStudio SSDT without errors" />
    <Delete Files="$(MSBuildThisFileDirectory)\obj\project.assets.json" />
  </Target>

</Project>

[ErikEJ]

@RyanThomas73 Thanks for sharing!

[EklipZgit]

This seems super weird that there's a Convert Existing doc that completely fails to mention that NONE of this works in Visual Studio, which is pretty much the only place most people deal with their existing .sqlproj's as far as I'm aware. Yes, I'm aware some of the docs ONLY mention ADS / VSCode, but this should still be called out explicitly as "you will need to remove your .sqlproj from your Visual Studio solution and manage it with one of these two tools instead, until the SSDT team....... or use these workarounds from this issue thread, where [....] will not work" or something. I did a bunch of reading on this before actually taking on the conversion and none of it prepared me for how much this breaks the visual studio flow.

I had a bunch of hacks in my old .sqlproj all for building from CLI, and removed them all on the basis of the convert existing guidelines saying to. In the end, seems like I need to add a bunch of hacks in the other direction now to keep it functional in in a VS .NET 8 project where it worked fine before with the old hacks. I get it if the VS team needs to a do a bunch of stuff of their own to support the new SDK, but at least call that out in the 'convert-existing' doc, with some links to these workarounds for all the people that have existing projects where they're obviously using SSDT in VS.

Will the VS SSDT updates happen before this gets out of -preview? Or is that not on any official roadmap?

[RyanThomas73]

@EklipZgit The bottom of the convert existing doc you linked does already state part of the steps needed here Even though build will honor the default globbing pattern, .sql files that are not included explicitly will not be displayed inside Solution Explorer in SSDT. Support for this will be added in a future release of SSDT

That being said, the .md doc could benefit from that callout about using it in conjunction with SSDT

1. Being moved to the top of the doc
2. And being made more visually prominent
3. And including a link to this thread so readers can find the latest and greatest comments on workarounds consumers have found