Database project fails to build with MSB4018: System.MissingMethodException unless Microsoft.SqlServer.TransactSql.ScriptDom.dll updated

[jasonhorner]

Type: Bug

it looks like the files in .azuredatastudio\extensions\microsoft.sql-database-projects-1.4.1\BuildDirectory might be using the wrong or an out of date version Microsoft.SqlServer.TransactSql.ScriptDom.dll

I had a project that was previously building successfully however it started throwing a lot of errors and no longer building: error MSB4018: System.MissingMethodException: Method not found: 'System.Collections.Generic.IList`1 Microsoft.SqlServer.TransactSql.ScriptDom.ViewHashDistributionPolicy.get_DistributionColumns()

it should be noted that the project in question is an Azure Synapse Dedicated Pool Database and was previously built with SSDT in VS2022.

after some trial and error we manually updated Microsoft.SqlServer.TransactSql.ScriptDom.dll from version: 161.8817.2 to version: 161.8910.0

and it fixed the issues:

1. Go to the extension in file explorer (e.g. C:\Users[user].azuredatastudio\extensions\microsoft.sql-database-projects-1.4.1\BuildDirectory
2. Remove the existing Microsoft.SqlServer.TransactSql.ScriptDom nupkg and dll files
3. Download the newest nuget package from nuget: https://www.nuget.org/packages/Microsoft.SqlServer.TransactSql.ScriptDom to the BuildDirectory
4. Replace the dll

it looks like the folder also contains *.nupkg which may not be needed?

Also is there a way to tell the extension to either override the version of the dacfx or automatically use the latest? I did see this https://learn.microsoft.com/en-us/azure-data-studio/extensions/sql-database-project-extension-build-from-command-line#non-sdk-style-sql-projects so maybe I can manually just update the files in DotNet Core build folder?

Extension version: 1.4.1 Azure Data Studio version: azuredatastudio 1.47.1 (b6f7beb01f92adaa4b79b6b6f3ac704e95cafe6e, 2024-01-10T02:15:02.628Z) OS version: Windows_NT x64 10.0.19045 Restricted Mode: No Preview Features: Enabled Modes:

System Info

|Item|Value| |---|---| |CPUs|Intel(R) Core(TM) i7-1065G7 CPU @ 1.30GHz (8 x 1498)| |GPU Status|2d_canvas: enabled
canvas_oop_rasterization: enabled_on
direct_rendering_display_compositor: disabled_off_ok
gpu_compositing: enabled
multiple_raster_threads: enabled_on
opengl: enabled_on
rasterization: enabled
raw_draw: disabled_off_ok
video_decode: enabled
video_encode: enabled
vulkan: disabled_off
webgl: enabled
webgl2: enabled
webgpu: enabled| |Load (avg)|undefined| |Memory (System)|31.60GB (14.69GB free)| |Process Argv|| |Screen Reader|no| |VM|0%|

[kisantia]

@ssreerama fyi. Looks like the scriptdom version needs to be updated here: https://github.com/microsoft/azuredatastudio/blob/824196876507416ed30e6bf504cdad4de431c0f5/extensions/sql-database-projects/src/tools/buildHelper.ts#L86

[jasonhorner]

@kisantia thanks for responding. I'm wondering if it makes sense to make the DACFX as whole configurable instead of just the scriptdom as mentioned in the code todo. probably a bigger effort but maybe more stable?

[kisantia]

For SDK-style projects, the Microsoft.Build.Sql SDK version can be specified in the .sqlproj. For legacy style projects, the DacFx dlls used to build the project come from extracting the DacFx dlls from the Microsoft.Build.Sql SDK, and that version can be specified with this setting:

But the actual version of DacFx used for building projects in ADS is limited to what's available in the Microsoft.Build.Sql SDK, unless you manually copy a different version of DacFx dlls to the build folder.

Another place for potential version mismatch is with publishing. The version used for publishing a project through ADS can also potentially be using a different version of DacFx than building, since it is specified in SqlToolsService: https://github.com/microsoft/sqltoolsservice/blob/main/Packages.props#L31