Make your SQL Server Database project (.sqlproj) build with .NET Core - even on Linux or macOS!

[utterances-bot]

Make your SQL Server Database project (.sqlproj) build with .NET Core - even on Linux or macOS! | ErikEJ's blog

A couple of years ago I blogged about a great community project that enables you to build a .dacpac using dotnet build, even on Linux and Mac.

https://erikej.github.io/ssdt/dotnet/2022/03/07/ssdt-dacpac-netcore.html

[JorgeCandeias]

Thanks for this post, it was somehow easier to find than the notes in the DacFX repository, go figure! Cheers.

[abbgrade]

This is nice. I tested it on a linux build agent - fastest database build ever :)
Unfortunately it breaks the build using Visual Studio 22 Pro on Windows for me.
It seems to ignore the TargetFrameworks property which is still dotnet framework.

[ErikEJ]

@abbgrade just target netstandard2.0 not net6.0

[abbgrade]

That worked. Thank you!

[rootzle2]

Mine won't build. I am getting 201 'unresolved reference' errors. If I revert the sqlproj file back to its original state, it builds fine. I am targeting .NET framework 2.0 as instructed in the comments a month ago.

Any words of wisdom?

[ErikEJ]

How are you building? Only dotnet build is currently supported reliably

[rootzle2]

"dotnet build /p:NetCoreBuild=true" I get 951 errors this way

[ErikEJ]

Removed bin and obj folders?

[rootzle2]

Steps to reproduce.
1. Unload the sqlproj from the solution. This leaves teh XML project definitino open
2. Add directly under the project tag. NOTE: the Sdk tag is underlined in green and and states "invalid child element."
3. Comment ""
4. Delete bin and obj folders
I even reinstalled the sqlpackage.exe with the MSI and verified it by going to the folder and typing sqlpackage.

[rootzle2]

5. Reload Project
6. open a command prompt, navigate to the folder the sln is in and type "dotnet build /p:NetCoreBuild=true"
7. See a flood of red and 951 errors

I know I am missing something dumb. this happens both in VS2019 and VS2022

Sorry for two posts, I accidentally hit enter instead of shift-enter

[ErikEJ]

Did you remove the Targets ?

Otherwise please share a repro project.

[rootzle2]

I commented the tags that reference "SqlTasks.targets." there were two after the closing

My apologies. I tried this on another project, and it worked flawlessly, even witht he target framework set to .NET Standard 4.5

[maxarendsen]

Thanks for the article. I updated our .NET Framework application to use the Microsoft.Build.Sql SDK. This setup works excellent for execution in the console and there are no (build) errors.

A problem occurs when I try to load the project with Visual Studio 2022. I get the message "Target framework not supported". It recommends to update the project to .NET Framework 4.8. I tried to change the .sqplproj to the sample file on the official GitHub page, changed the target framework, but nothing seems to work. Do you have any suggestions to fix this problem?

[ErikEJ]

@maxarendsen without a repro project I do not have any suggestions - you can email me privately or share here.

[maxarendsen]

@ErikEJ I created a simple repro repository. See https://github.com/maxarendsen/repro-sql-sdk-vs22. Executing the project with dotnet build /p:NetCoreBuild=true works as expected. Loading the project in Visual Studio gives the error as explained before.

[ErikEJ]

@maxarendsen That .sqlproj looks nothing like a VS .sqlproj - and does not follow the blog post format either.

Create a blank .sqlproj in VS and start from that.

[maxarendsen]

@ErikEJ The updated repository works in Visual Studio and in the command line. Sorry, I was a bit sidetracked by trying to fix another problem I had with building the project. The error I got was that trying to build the project with Visual Studio gave me an error as:

Your project does not reference ".NETFramework,Version=v4.7.2" framework. Add a reference to ".NETFramework,Version=v4.7.2" in the "TargetFrameworks" property of your project file and then re-run NuGet restore. RepoDatabase C:\Program Files\Microsoft Visual Studio\2022\Preview\MSBuild\Microsoft\NuGet\17.0\Microsoft.NuGet.targets 198

This had me sidetracked by trying to update the .sqlproj.

Is this error with trying to build a known issue, and does this mean that only building with the command line dotnet build /p:NetCoreBuild=true works?

[ErikEJ]

Currently only command line build is supported, yes. Is that not mentioned in the blog post?

[maxarendsen]

@ErikEJ It states that it is possible to build with the command line, but does not say that building/publishing with Visual Studio doesn't work. Maybe a good addition?

[pipbrandy]

Hello,

I have implemented you fix above. I am attempting to run this in a Docker container with Ubuntu 20.4 with MSSQL installed, all the appropriate sql tools and dot-net sdk 6.0, etc. When I attempt to build a Sql Data Project, I get this error:

/usr/share/dotnet/sdk/6.0.401/Microsoft.Common.CurrentVersion.targets(1220,5): error MSB3644: The reference assemblies for .NETFramework,Version=v4.6 were not found. To resolve this, install the Developer Pack (SDK/Targeting Pack) for this framework version or retarget your application. You can download .NET Framework Developer Packs at https://aka.ms/msbuild/developerpacks [/var/opt/mssql/dbproj/marketplace.sqlproj]
/usr/share/dotnet/sdk/6.0.401/Microsoft.Common.CurrentVersion.targets(5647,5): error MSB3191: Unable to create directory "obj/Debug/". Read-only file system : '/var/opt/mssql/dbproj/obj/Debug/' [/var/opt/mssql/dbproj/marketplace.sqlproj]

I have attempted to install specifically the "4.6" dotnet SDK with this command, but I keep getting an error that it cannot resolve that version (I've also tried 4.6.2):

root@sql-server:/# ./dotnet-install.sh -c 4.6.0
dotnet-install: Note that the intended use of this script is for Continuous Integration (CI) scenarios, where:
dotnet-install: - The SDK needs to be installed without user interaction and without admin rights.
dotnet-install: - The SDK installation doesn't need to persist across multiple CI runs.
dotnet-install: To set up a development environment or to run apps, use installers rather than this script. Visit https://dotnet.microsoft.com/download to get the installer.

dotnet_install: Error: Failed to resolve the exact version number.

This is what I have on my Ubuntu Container:

root@sql-server:/# dotnet --info
.NET SDK (reflecting any global.json):
 Version:   6.0.401
 Commit:    0906eae6f8

Runtime Environment:
 OS Name:     ubuntu
 OS Version:  20.04
 OS Platform: Linux
 RID:         ubuntu.20.04-x64
 Base Path:   /usr/share/dotnet/sdk/6.0.401/

global.json file:
  Not found

Host:
  Version:      6.0.9
  Architecture: x64
  Commit:       163a63591c

.NET SDKs installed:
  6.0.401 [/usr/share/dotnet/sdk]

.NET runtimes installed:
  Microsoft.AspNetCore.App 6.0.9 [/usr/share/dotnet/shared/Microsoft.AspNetCore.App]
  Microsoft.NETCore.App 6.0.9 [/usr/share/dotnet/shared/Microsoft.NETCore.App]

So, I need to know either a) how to find the exact sdk version for 4.6 or b) how to retarget my .sqlproj file to use the version of the sdk I do have (6.0.9). I'll post my .sqlproj file in the next comment.

[pipbrandy]

.sqlproj file:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">
  <Sdk Name="Microsoft.Build.Sql" Version="0.1.3-preview" />
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <Name>[name]</Name>
    <SchemaVersion>2.0</SchemaVersion>
    <ProjectVersion>4.1</ProjectVersion>
    <ProjectGuid>{865835ed-3040-4373-97d7-e59fbe5fab21}</ProjectGuid>
    <DSP>Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider</DSP>
    <OutputType>Database</OutputType>
    <RootPath>
    </RootPath>
    <RootNamespace>[name]</RootNamespace>
    <AssemblyName>[name]</AssemblyName>
    <ModelCollation>1033,CI</ModelCollation>
    <DefaultFileStructure>BySchemaAndSchemaType</DefaultFileStructure>
    <DeployToDatabase>True</DeployToDatabase>
    <TargetFrameworkVersion>v4.5</TargetFrameworkVersion>
    <TargetLanguage>CS</TargetLanguage>
    <AppDesignerFolder>Properties</AppDesignerFolder>
    <SqlServerVerification>False</SqlServerVerification>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseSet>True</TargetDatabaseSet>
    <DefaultCollation>SQL_Latin1_General_CP1_CI_AS</DefaultCollation>
    <AnsiNulls>False</AnsiNulls>
    <QuotedIdentifier>False</QuotedIdentifier>
    <QueryStoreCaptureMode>Auto</QueryStoreCaptureMode>
    <QueryStoreDesiredState>Off</QueryStoreDesiredState>
    <QueryStoreFlushInterval>900</QueryStoreFlushInterval>
    <QueryStoreStatsInterval>60</QueryStoreStatsInterval>
    <QueryStoreMaxPlansPerQuery>200</QueryStoreMaxPlansPerQuery>
    <QueryStoreStaleQueryThreshold>30</QueryStoreStaleQueryThreshold>
    <QueryStoreMaxStorageSize>1000</QueryStoreMaxStorageSize>
    <DbScopedConfigLegacyCardinalityEstimation>Off</DbScopedConfigLegacyCardinalityEstimation>
    <DbScopedConfigMaxDOP>0</DbScopedConfigMaxDOP>
    <DbScopedConfigParameterSniffing>On</DbScopedConfigParameterSniffing>
    <DbScopedConfigOptimizerHotfixes>Off</DbScopedConfigOptimizerHotfixes>
    <DelayedDurability>DISABLED</DelayedDurability>
    <AutoCreateStatisticsIncremental>False</AutoCreateStatisticsIncremental>
    <MemoryOptimizedElevateToSnapshot>False</MemoryOptimizedElevateToSnapshot>
    <Containment>None</Containment>
    <IsNestedTriggersOn>True</IsNestedTriggersOn>
    <IsTransformNoiseWordsOn>False</IsTransformNoiseWordsOn>
    <TwoDigitYearCutoff>2049</TwoDigitYearCutoff>
    <NonTransactedFileStreamAccess>OFF</NonTransactedFileStreamAccess>
    <TargetRecoveryTimePeriod>60</TargetRecoveryTimePeriod>
    <TargetRecoveryTimeUnit>SECONDS</TargetRecoveryTimeUnit>
    <IsChangeTrackingOn>False</IsChangeTrackingOn>
    <IsChangeTrackingAutoCleanupOn>True</IsChangeTrackingAutoCleanupOn>
    <ChangeTrackingRetentionPeriod>2</ChangeTrackingRetentionPeriod>
    <ChangeTrackingRetentionUnit>Days</ChangeTrackingRetentionUnit>
    <IsEncryptionOn>False</IsEncryptionOn>
    <IsBrokerPriorityHonored>False</IsBrokerPriorityHonored>
    <Trustworthy>False</Trustworthy>
    <AutoUpdateStatisticsAsynchronously>False</AutoUpdateStatisticsAsynchronously>
    <PageVerify>CHECKSUM</PageVerify>
    <ServiceBrokerOption>EnableBroker</ServiceBrokerOption>
    <DateCorrelationOptimizationOn>False</DateCorrelationOptimizationOn>
    <Parameterization>SIMPLE</Parameterization>
    <AllowSnapshotIsolation>False</AllowSnapshotIsolation>
    <ReadCommittedSnapshot>False</ReadCommittedSnapshot>
    <VardecimalStorageFormatOn>True</VardecimalStorageFormatOn>
    <SupplementalLoggingOn>False</SupplementalLoggingOn>
    <CompatibilityMode>150</CompatibilityMode>
    <AnsiNullDefault>False</AnsiNullDefault>
    <AnsiPadding>False</AnsiPadding>
    <AnsiWarnings>False</AnsiWarnings>
    <ArithAbort>False</ArithAbort>
    <ConcatNullYieldsNull>False</ConcatNullYieldsNull>
    <NumericRoundAbort>False</NumericRoundAbort>
    <RecursiveTriggersEnabled>False</RecursiveTriggersEnabled>
    <DatabaseChaining>False</DatabaseChaining>
    <DatabaseState>ONLINE</DatabaseState>
    <CloseCursorOnCommitEnabled>False</CloseCursorOnCommitEnabled>
    <DefaultCursor>GLOBAL</DefaultCursor>
    <AutoClose>False</AutoClose>
    <AutoCreateStatistics>True</AutoCreateStatistics>
    <AutoShrink>False</AutoShrink>
    <AutoUpdateStatistics>True</AutoUpdateStatistics>
    <TornPageDetection>False</TornPageDetection>
    <DatabaseAccess>MULTI_USER</DatabaseAccess>
    <Recovery>FULL</Recovery>
    <EnableFullTextSearch>True</EnableFullTextSearch>
    <DefaultFilegroup>PRIMARY</DefaultFilegroup>
    <CreateProjectFromDatabaseCompleted>True</CreateProjectFromDatabaseCompleted>
    <DacVersion>0.0.0.0</DacVersion>
    <DacDescription></DacDescription>
    <GenerateCreateScript>True</GenerateCreateScript>
    <DacApplicationName>[name]</DacApplicationName>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
    <OutputPath>bin\Release\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>False</TreatWarningsAsErrors>
    <DebugType>pdbonly</DebugType>
    <Optimize>true</Optimize>
    <DefineDebug>false</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <OutputPath>bin\Debug\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>false</TreatWarningsAsErrors>
    <DebugSymbols>true</DebugSymbols>
    <DebugType>full</DebugType>
    <Optimize>false</Optimize>
    <DefineDebug>true</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
    <DataRetentionEnabled>False</DataRetentionEnabled>
    <SqlTargetName>db_v_0_0_0</SqlTargetName>
  </PropertyGroup>
  <PropertyGroup>
    <VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">11.0</VisualStudioVersion>
    <!-- Default to the v11.0 targets path if the targets file for the current VS version is not found -->
    <!-- <SSDTExists Condition="Exists('$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets')">True</SSDTExists> -->
    <VisualStudioVersion Condition="'$(SSDTExists)' == ''">11.0</VisualStudioVersion>
  </PropertyGroup>
  <!-- <Import Condition="'$(NetCoreBuild)' != 'true' AND '$(SQLDBExtensionsRefPath)' != ''" Project="$(SQLDBExtensionsRefPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
  <Import Condition="'$(NetCoreBuild)' != 'true' AND '$(SQLDBExtensionsRefPath)' == ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" /> -->
  <ItemGroup>
    <Folder Include="Properties" />
    <Folder Include="dbo\" />
    <Folder Include="dbo\Tables\" />
    <Folder Include="dbo\Storage\" />
    <Folder Include="scripts" />
    <Folder Include="publish_profiles" />
  </ItemGroup>
  <ItemGroup>
    [redacted]
  </ItemGroup>
  <ItemGroup>
    <None Include="publish_profiles\publish.xml" />
  </ItemGroup>
  <!-- <Import Condition="'$(NetCoreBuild)' == 'true'" Project="$(NETCoreTargetsPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" /> -->
  <ItemGroup>
    <PackageReference Condition="'$(NetCoreBuild)' == 'true'" Include="Microsoft.NETFramework.ReferenceAssemblies" Version="1.0.0" PrivateAssets="All" />
  </ItemGroup>
  <Target Name="BeforeBuild">
    <Delete Files="$(BaseIntermediateOutputPath)\project.assets.json" />
  </Target>
</Project>

[ErikEJ]

@pipbrandy suggest you post an issue in the DacFX repo

[gzinger]

It's been 2 years since initial announcement. Any idea when this technology would work for regular Visual Studio (not VS Code)? Thanks.

[ErikEJ]

@dzsquared any roadmap for the VS update?

[reckface]

@pipbrandy You just needed the <TargetFramework>netstandard2.1</TargetFramework> at least. In the first property group near the top. Most of the other elements are no longer necessary.

[JinsPeter]

@dzsquared Any update on the VS SSDT tools to support this? and on the RTM release?

[dzsquared]

@JinsPeter - soon! like, soon soon. so soon, in fact, that we launched a new section in the docs https://aka.ms/sqlprojects

microsoft/DacFx#180 (comment)

[palapiessa]

Well done @dzsquared and @ErikEJ! With your help I was able to build on MacOS, a great surprise to me.