You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Have a database with memory optimized table in SSDT.
Create a dacpac file via build.
Spin up a new container (any version of SQL)
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
@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.
16.0.6161.0
- .NET Framework : 4.7.1Steps to Reproduce:
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
The text was updated successfully, but these errors were encountered: