Create memory optimized tables ERROR

Hello,
I work with MSSQL and I need to create a procedure to create a Type memory optimized tables.

Liquibase produces an error of type

Error :
Unexpected error running Liquibase: DDL statements ALTER, DROP and CREATE inside user transactions are not supported with memory optimized tables. [Failed SQL: (12331)

How to add this kind of object with liquibase.

Thanks for your help

Can you provide the changeset so we can see what you are trying to do? I’m assuming you are trying to use a Liquibase change-type to create this type of object, maybe you need to just use custom SQL instead.

Hi, there,
My knowledge of Liquibase is poor.
I think you want the “dbchangelog.xml” files.

Here is the basic one:

<?xml version="1.0" encoding="UTF-8"?>
  <databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
	<include file="./01_System/dbchangelog.xml" relativeToChangelogFile="true"/>
	<include file="./02_Table/dbchangelog.xml" relativeToChangelogFile="true"/>
	<includeAll path="03_Function" relativeToChangelogFile="true" />
	<includeAll path="04_View" relativeToChangelogFile="true" />
	<includeAll path="05_StoreProcedure" relativeToChangelogFile="true" />
	<include file="./06_Index/dbchangelog.xml" relativeToChangelogFile="true"/>
	<includeAll path="07_Trigger" relativeToChangelogFile="true" />
	<include file="./99_Misc/dbchangelog.xml" relativeToChangelogFile="true"/>
  </databaseChangeLog>

And the one in the 01_System directory where the code is

<?xml version="1.0" encoding="UTF-8"?>
  <databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
	
	<changeSet author="mba" id="Add Type apprenticeship_Type">
		<validCheckSum>8:218491c3d244ac25fbb7f479ac9356f8</validCheckSum>
		<sqlFile 
				endDelimiter="\nGO"
				path="apprenticeship_Type.sql"
				relativeToChangelogFile="true"
				splitStatements="true"
				stripComments="true"/>
	</changeSet>

  </databaseChangeLog>

And the SQL file

/*----------------------------------------------------
Author:		mba
Date:		20.09.2021
Description:Add a data type for create table parameters
-----------------------------------------------------*/
-- Enable MEMORY_OPTIMIZED on the DB
/*
-- Creating a Memory Optimized Table
-- *********************************
--The key to having a table “In-Memory” is the use of the key word “MEMORY-OPTIMIZED” on the create statement when you first create the table. Note there is no ability to ALTER a table to make an existing one memory optimized; you will need to recreate the table and load the data in order to take advantage of this option on an existing table.  There are just a couple more settings you need to have configured to make this work as you can see from below.
-- **************************************************************

ALTER DATABASE searchService_test SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
ALTER DATABASE searchService_test ADD FILEGROUP searchService_test_mod CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE searchService_test ADD FILE (name='searchService_test_mod1', 
	filename='d:\data\searchService_test) TO FILEGROUP searchService_test_mod
*/
--GO

--DROP TYPE IF EXISTS [dbo].[apprenticeship_Type]

GO
IF TYPE_ID(N'apprenticeship_Type') IS NULL
BEGIN
/****** Object:  UserDefinedTableType [dbo].[apprenticeship_Type]    Script Date: 19.09.2021 21:53:52 ******/
	CREATE TYPE [dbo].[apprenticeship_Type] AS TABLE(
		[ID_] [int] IDENTITY(1,1) NOT NULL,
		[id] [int] NOT NULL,
		[id_apprenticeship] [int] NOT NULL,
		[name_de_mf] [nvarchar](255) COLLATE Latin1_General_CI_AI NOT NULL,
		[name_fr_mf] [nvarchar](255) COLLATE Latin1_General_CI_AI NOT NULL,
		[name_it_mf] [nvarchar](255) COLLATE Latin1_General_CI_AI NOT NULL,
		INDEX [IX_appenticesship_Type_ID_] NONCLUSTERED 
		(
			[ID_] ASC
		),
			INDEX [IX_appenticesship_Type_name_de_mf] NONCLUSTERED 
		(
			[name_de_mf] ASC
		),
			INDEX [IX_appenticesship_Type_name_fr_mf] NONCLUSTERED 
		(
			[name_fr_mf] ASC
		),
			INDEX [IX_appenticesship_Type_name_it_mf] NONCLUSTERED 
		(
			[name_it_mf] ASC
		),
			 PRIMARY KEY NONCLUSTERED 
		(
			[id] ASC
		)
	)
	WITH ( MEMORY_OPTIMIZED = ON )
END
GO

OK, that error is actually a SQL-Server error, not a Liquibase error.

So I think this might be the solution… add this attribute inside the “sqlFile” section: runInTransaction=“false”

I add your parameter on sqlFile section like that :slight_smile:

<?xml version="1.0" encoding="UTF-8"?>
  <databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
	
	<changeSet author="mba" id="Add Type apprenticeship_Type">
		<sqlFile 
			runInTransaction="false"
			endDelimiter="\nGO"
			path="apprenticeship_Type.sql"
			relativeToChangelogFile="true"
			splitStatements="true"
			stripComments="true"
			runOnChange="true"/>
	</changeSet>

And I have yet this answer :

Liquibase Community 4.4.3 by Datical
Unexpected error running Liquibase: Error parsing line 16 column 24 of v1.10.x/01_System/dbchangelog.xml: cvc-complex-type.3.2.2 : L'attribut 'runInTransaction' n'est pas autorisé dans l'élément 'sqlFile'.