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

ErikEJ · 2022-11-11T07:14:22Z

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

Steps to Reproduce:

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

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

Real-JD-UK · 2022-11-14T22:44:42Z

@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 · 2022-11-15T06:06:28Z

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

ssreerama · 2022-11-16T19:43:49Z

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 · 2022-11-16T19:47:21Z

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 · 2022-11-16T20:56:03Z

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 · 2022-11-30T23:04:12Z

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

CSharpFiasco · 2023-01-07T18:54:48Z

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 · 2023-08-09T01:37:36Z

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. .\sql</OutputPath>), it won't load the project. Has anyone seen this issue?

Nathalilly · 2023-09-12T10:10:44Z

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 · 2024-01-25T15:12:43Z

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 · 2024-03-08T22:51:54Z

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 · 2024-03-20T16:45:53Z

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 · 2024-03-20T16:55:41Z

@RyanThomas73 Thanks for sharing!

EklipZgit · 2024-04-10T21:00:40Z

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 · 2024-04-10T23:25:31Z

@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

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

czb182 · 2024-06-07T15:38:07Z

Tried 1.19-preview with vs2022 17.10.1

get (d
efault target) (14) ->
(ResolveNuGetPackageAssets target) ->
C:\Program Files\Microsoft Visual Studio\2022\Enterprise\MSBuild\Microsoft\NuGet\17.0\Microsoft.NuGet.targets(198,5):
error : Your project does not reference ".NETFramework,Version=v4.7.2" framework. Add a reference to ".NETFramework,Ve
rsion=v4.7.2" in the "TargetFrameworks" property of your project file and then re-run NuGet restore.

If I try passing "/p:NetCoreBuild=true" to get around, get this isntead

C:\Users\cmire.nuget\packages\microsoft.build.sql\0.1.19-preview\Sdk\Sdk.targets(52,3): error MSB4019: The imported project "C:\Program Files\Microsoft Visual Studio\2022\Enterprise\MSBuild\Sdks\Microsoft.NET.Sdk\targets\Microsoft.NET.DefaultAssemblyInfo.targets" was not found. Confirm that the expression in the Import declaration "C:\Program Files\Microsoft Visual Studio\20
22\Enterprise\MSBuild\Sdks/Microsoft.NET.Sdk/targets/Microsoft.NET.DefaultAssemblyInfo.targets" is correct, and that the file exists on disk.

andrew-sumner · 2024-06-16T11:20:09Z

Working example for 1.19-preview with vs2022 17.10.2 (Community Edition)

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="0.1.19-preview" />
  <PropertyGroup>
    <Name>MySample</Name>
    <ProjectGuid>{71b1081a-ce12-4fc4-9c9c-53bf630fc0d7}</ProjectGuid>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <TargetDatabaseSet>True</TargetDatabaseSet>
  </PropertyGroup>
  <Target Name="BeforeBuild">
    <Delete Files="$(BaseIntermediateOutputPath)\project.assets.json" />
  </Target>
  
  <!-- 
   Support Visual Studio.
   Solution is a mix of example from @RyanThomas73, and a recent commit to DaxFx with support for Visual Studio:
    * https://github.com/microsoft/DacFx/issues/180#issuecomment-2010033767 
    * https://github.com/microsoft/DacFx/commit/3cd56f7c173457ed52e76979dddf901e7285f090
  -->
  <!-- building in Visual Studio requires some sort of TargetFrameworkVersion. So we condition to NetCoreBuild as false to avoid failures -->
  <PropertyGroup Condition="'$(MSBuildRuntimeType)' != 'Core'">
    <TargetFrameworkVersion>v4.7.2</TargetFrameworkVersion>
    <TargetFrameworkMoniker>.NETFramework,Version=$(TargetFrameworkVersion)</TargetFrameworkMoniker>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
  </PropertyGroup>
  <ItemGroup Condition="'$(MSBuildRuntimeType)' != 'Core'">
    <!-- Build default globbing pattern includes all sql files in all subfolders, excluding bin and obj, visual studio is likely to mess with this... -->
    <Build Include="**/*.sql" />
    <Build Remove="bin/**/*.sql" />
    <Build Remove="obj/**/*.sql" />
  </ItemGroup>
  <ItemGroup>
    <Folder Include="Stored Procedures\" />
    <Folder Include="Tables\" />
  </ItemGroup>
  <!-- 
    Building through visual studio will fail with `Your project does not reference ".NETFramework,Version=v4.7.2" framework.` if a previous build through the dotnet core cli
    has run and created the obj/project.assets.json file. This custom target will 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>

andrew-sumner · 2024-06-18T12:49:15Z

While this has been an interesting exercise, Visual Studio still lacks nuget package support so guess I'm stuck waiting for an official release :-(

dzsquared · 2024-08-26T16:42:11Z

Preview support for the Microsoft.Build.Sql SDK is landing in Visual Studio soon - will update ya'll as soon as the first release goes out the door!

dzsquared · 2024-09-10T18:09:35Z

With Visual Studio 17.12 preview 2 - support for Microsoft.Build.Sql SDK-style projects is now available in preview for Visual Studio.

2 key limitations to note:

in preview 2 the file extension for the project is .sqlprojx instead of .sqlproj
to use side-by-side with the original SQL projects in Visual Studio, install separate instances of VS on your machine

blog: https://techcommunity.microsoft.com/t5/azure-sql-blog/preview-release-of-sdk-style-sql-projects-in-visual-studio-2022/ba-p/4240616

docs: https://aka.ms/ssdt-sdk-preview

ErikEJ · 2024-09-10T18:13:35Z

@dzsquared already?? 😉 - this is amazing!

dzsquared · 2024-09-10T19:49:20Z

@dzsquared already?? 😉 - this is amazing!

it seems like just yesterday 0.1.1-alpha was released..... but according to my notes - https://www.nuget.org/packages/Microsoft.Build.Sql/0.1.1-alpha - it was almost 3 years ago 😄

ErikEJ added the bug Something isn't working label Nov 11, 2022

kisantia assigned ssreerama Nov 16, 2022

ErikEJ closed this as not planned Won't fix, can't repro, duplicate, stale Nov 27, 2022

ssreerama added the area: build sdk Related to Microsoft.Build.Sql SDK label Nov 30, 2022

dzsquared added enhancement New feature or request and removed bug Something isn't working labels Nov 30, 2022

dzsquared unassigned ssreerama Nov 30, 2022

dzsquared reopened this Nov 30, 2022

dzsquared changed the title ~~Unable to build in VS~~ Unable to build in VS - Visual Studio support for Microsoft.Build.Sql Nov 30, 2022

dzsquared mentioned this issue Jan 25, 2024

Why not support VS proper? MicrosoftDocs/data-tools#16

Closed

dzsquared added the area: ssdt Related to the use of DacFx in Visual Studio (SSDT) label Feb 6, 2024

dzsquared mentioned this issue Mar 12, 2024

Adding existing project causes VS 2022 to hang #425

Closed

This was referenced Jun 7, 2024

SDK-style sqlproj is not integrated well with VS 2019 Solution Explorer #307

Closed

Building SSDT project for with dotnet tooling (Microsoft.Build.Sql - 0.1.3-preview) #98

Closed

Updating Microsoft.Build.Sql for utilising in Visual Studio #443

Merged

dzsquared mentioned this issue Jul 12, 2024

TargetDatabaseSet property adds #277

Open

IVNSTN mentioned this issue Jul 22, 2024

SqlPackage produces huge deployment script and diff report for inmemory procs and functions #467

Closed

dzsquared mentioned this issue Sep 9, 2024

Make your SQL Server Database project (.sqlproj) build with .NET Core - even on Linux or macOS! ErikEJ/erikej.github.io#44

Open

ErikEJ closed this as completed Sep 10, 2024

robertmclaws mentioned this issue Sep 17, 2024

Update Project Template #490

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

ErikEJ commented Nov 11, 2022 •

edited

Loading

Real-JD-UK commented Nov 14, 2022

ErikEJ commented Nov 15, 2022

ssreerama commented Nov 16, 2022 •

edited

Loading

ErikEJ commented Nov 16, 2022

ssreerama commented Nov 16, 2022

dzsquared commented Nov 30, 2022

CSharpFiasco commented Jan 7, 2023 •

edited

Loading

nholland20 commented Aug 9, 2023 •

edited

Loading

Nathalilly commented Sep 12, 2023 •

edited

Loading

gzinger commented Jan 25, 2024 •

edited

Loading

nholland20 commented Mar 8, 2024

RyanThomas73 commented Mar 20, 2024

ErikEJ commented Mar 20, 2024

EklipZgit commented Apr 10, 2024 •

edited

Loading

RyanThomas73 commented Apr 10, 2024

czb182 commented Jun 7, 2024

andrew-sumner commented Jun 16, 2024 •

edited

Loading

andrew-sumner commented Jun 18, 2024

dzsquared commented Aug 26, 2024

dzsquared commented Sep 10, 2024

ErikEJ commented Sep 10, 2024

dzsquared commented Sep 10, 2024

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

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

Comments

ErikEJ commented Nov 11, 2022 • edited Loading

Real-JD-UK commented Nov 14, 2022

ErikEJ commented Nov 15, 2022

ssreerama commented Nov 16, 2022 • edited Loading

ErikEJ commented Nov 16, 2022

ssreerama commented Nov 16, 2022

dzsquared commented Nov 30, 2022

CSharpFiasco commented Jan 7, 2023 • edited Loading

nholland20 commented Aug 9, 2023 • edited Loading

Nathalilly commented Sep 12, 2023 • edited Loading

gzinger commented Jan 25, 2024 • edited Loading

nholland20 commented Mar 8, 2024

RyanThomas73 commented Mar 20, 2024

ErikEJ commented Mar 20, 2024

EklipZgit commented Apr 10, 2024 • edited Loading

RyanThomas73 commented Apr 10, 2024

czb182 commented Jun 7, 2024

andrew-sumner commented Jun 16, 2024 • edited Loading

andrew-sumner commented Jun 18, 2024

dzsquared commented Aug 26, 2024

dzsquared commented Sep 10, 2024

ErikEJ commented Sep 10, 2024

dzsquared commented Sep 10, 2024

ErikEJ commented Nov 11, 2022 •

edited

Loading

ssreerama commented Nov 16, 2022 •

edited

Loading

CSharpFiasco commented Jan 7, 2023 •

edited

Loading

nholland20 commented Aug 9, 2023 •

edited

Loading

Nathalilly commented Sep 12, 2023 •

edited

Loading

gzinger commented Jan 25, 2024 •

edited

Loading

EklipZgit commented Apr 10, 2024 •

edited

Loading

andrew-sumner commented Jun 16, 2024 •

edited

Loading