utterances-bot commented 2 years ago

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 commented 2 years ago

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

abbgrade commented 2 years ago

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 commented 2 years ago

@abbgrade just target netstandard2.0 not net6.0

abbgrade commented 2 years ago

That worked. Thank you!

rootzle2 commented 2 years ago

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 commented 2 years ago

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

rootzle2 commented 2 years ago

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

ErikEJ commented 2 years ago

Removed bin and obj folders?

rootzle2 commented 2 years ago

Steps to reproduce.

Unload the sqlproj from the solution. This leaves teh XML project definitino open
Add directly under the project tag. NOTE: the Sdk tag is underlined in green and and states "invalid child element."
Comment ""
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 commented 2 years ago

Reload Project
open a command prompt, navigate to the folder the sln is in and type "dotnet build /p:NetCoreBuild=true"
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 commented 2 years ago

Did you remove the Targets ?

Otherwise please share a repro project.

rootzle2 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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

maxarendsen commented 2 years ago

@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 commented 2 years ago

@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 commented 2 years ago

@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 commented 2 years ago

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

maxarendsen commented 2 years ago

@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 commented 1 year ago

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 commented 1 year ago

.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 commented 1 year ago

@pipbrandy suggest you post an issue in the DacFX repo

gzinger commented 8 months ago

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

ErikEJ commented 8 months ago

@dzsquared any roadmap for the VS update?

reckface commented 6 months ago

@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 commented 1 week ago

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

dzsquared commented 1 week ago

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

https://github.com/microsoft/DacFx/issues/180#issuecomment-2310626410

ErikEJ / erikej.github.io

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

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

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:

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):

This is what I have on my Ubuntu Container:

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.

.sqlproj file: