DefaultDataPath and DefaultLogPath not set

[gertcoet]

• SqlPackage or DacFx Version:

16.0.6161.0

- .NET Framework : 4.7.1

Steps to Reproduce:

1. Have a database with memory optimized table in SSDT.
2. Create a dacpac file via build.
3. Spin up a new container (any version of SQL)
4. Try and deploy with DacFx library

Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 5121, Level 16, State 2, Line 1 The path specified by "USS_Settings_FileGroupMemoryOptimized_203A1A4F.mdf" is not in a valid directory. Error SQL72045: Script execution error. The executed script: ALTER DATABASE [$(DatabaseName)] ADD FILE (NAME = [FileGroupMemoryOptimized_203A1A4F], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_FileGroupMemoryOptimized_203A1A4F.mdf') TO FILEGROUP [FileGroupMemoryOptimized];

Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 5009, Level 16, State 14, Line 1 One or more files listed in the statement could not be found or could not be initialized. Error SQL72045: Script execution error. The executed script: ALTER DATABASE [$(DatabaseName)] ADD FILE (NAME = [FileGroupMemoryOptimized_203A1A4F], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_FileGroupMemoryOptimized_203A1A4F.mdf') TO FILEGROUP [FileGroupMemoryOptimized

The error is because the generate script have the following variables set to empty

:setvar DbEnvironment "Development" :setvar DefaultDataPath "" :setvar DefaultLogPath ""

Even when setting them, they are not pulled into the script.

dacDeployOptions.SqlCommandVariableValues.Add("DbEnvironment", "Development"); dacDeployOptions.SqlCommandVariableValues.Add("DefaultDataPath", "/var/opt/mssql/data/"); dacDeployOptions.SqlCommandVariableValues.Add("DefaultLogPath", "/var/opt/mssql/log/");

**Did this occur in prior versions? Yes

DacFx

[namangupta211]

@gertcoet I am not able to repro the issue. I am able to generate script with both DefaultDataPath, DefaultLogPath. I have some questions:

1. Are you using a docker container, if so, which image are you using?
2. is it on Linux?

[gertcoet]

@namangupta211, did you create a memory optimized table and filegroup? I created an example project you can use here. The docker image I use is this one - mcr.microsoft.com/mssql/server:2019-CU14-ubuntu-20.04

Once you have the project, you can deploy it to a Windows version of SQL Server using something like sqlpackage /SourceFile:"C:\dev\Playground\DBTest\DBTest\bin\Debug\DBTest.dacpac" /Action:Publish /tcs:"Data Source=GamingDB9;Persist Security Info=True;User ID=sa;password=####;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False;Initial Catalog=master" /dsp:"c:\temp\testdb.sql" and it works. (Or you can just deploy from Visual Studio)

Deploying to the Docker(Linux) image with something like sqlpackage /SourceFile:"C:\dev\Playground\DBTest\DBTest\bin\Debug\DBTest.dacpac" /Action:Publish /tcs:"Data Source=localhost;Persist Security Info=True;User ID=sa;password=####;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False;Initial Catalog=master" /dsp:"c:\temp\testdb.sql" gives the following error. (Again you can use Visual Studio as well)

*** Could not deploy package. Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 1826, Level 16, State 1, Line 1 User-defined filegroups are not allowed on "master". Error SQL72045: Script execution error. The executed script: ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP [MemOptfilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

If you run the command to create a script and not deploy with /Action:Scripts, you will see this in the Docker version of the output script.

GO
:setvar DatabaseName "master"
:setvar DefaultFilePrefix "master"
:setvar DefaultDataPath ""
:setvar DefaultLogPath ""

GO

The values are populated for the Windows version of the script. Updating them with "/var/opt/mssql/data/" manually, makes the script run fine.

As per my previous post, trying to update theses values using the Nuget does not populate the values either.

dacDeployOptions.SqlCommandVariableValues.Add("DefaultDataPath", "/var/opt/mssql/data/");
dacDeployOptions.SqlCommandVariableValues.Add("DefaultLogPath", "/var/opt/mssql/log/");