# Create memory optimized tables ERROR

**URL:** <https://forum.liquibase.org/t/create-memory-optimized-tables-error/5994>\
**Category:** General Discussion\
**Created:** [October 4, 2021, 4:36pm UTC](https://forum.liquibase.org/t/create-memory-optimized-tables-error/5994 "2021-10-04T16:36:49Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![mib](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/mib/32/468_2.png) [@mib](https://forum.liquibase.org/u/mib)\
**Post date:** [October 4, 2021, 4:36pm UTC](https://forum.liquibase.org/t/create-memory-optimized-tables-error/5994/1 "2021-10-04T16:36:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![daryldoak](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/daryldoak/32/764_2.png) [@daryldoak](https://forum.liquibase.org/u/daryldoak)\
**Post date:** [October 5, 2021, 11:16am UTC](https://forum.liquibase.org/t/create-memory-optimized-tables-error/5994/2 "2021-10-05T11:16:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mib](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/mib/32/468_2.png) [@mib](https://forum.liquibase.org/u/mib)\
**Post date:** [October 5, 2021, 11:27am UTC](https://forum.liquibase.org/t/create-memory-optimized-tables-error/5994/3 "2021-10-05T11:27:18Z")

</div>

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

Here is the basic one:

```auto
<?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

```auto
<?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

```auto
/*----------------------------------------------------
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

```

---

<div class="post-metadata">

**Author:** ![daryldoak](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/daryldoak/32/764_2.png) [@daryldoak](https://forum.liquibase.org/u/daryldoak)\
**Post date:** [October 5, 2021, 12:01pm UTC](https://forum.liquibase.org/t/create-memory-optimized-tables-error/5994/4 "2021-10-05T12:01:28Z")

</div>

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”

---

<div class="post-metadata">

**Author:** ![mib](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/mib/32/468_2.png) [@mib](https://forum.liquibase.org/u/mib)\
**Post date:** [October 5, 2021, 5:20pm UTC](https://forum.liquibase.org/t/create-memory-optimized-tables-error/5994/5 "2021-10-05T17:20:25Z")

</div>

I add your parameter on sqlFile section like that 🙂

```auto
<?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 :

```auto
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'.

```

---

<div class="post-metadata">

**Author:** ![MikeOlivas](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/mikeolivas/32/183_2.png) [@MikeOlivas](https://forum.liquibase.org/u/MikeOlivas)\
**Post date:** [December 30, 2021, 6:51pm UTC](https://forum.liquibase.org/t/create-memory-optimized-tables-error/5994/6 "2021-12-30T18:51:33Z")

</div>

> [@mib](#):
>
> `runInTransaction="false"`

That error means that sqlFile element does not support that attribute.  
runInTransaction is an attribute of the changeSet element. Try to move it to the changeSet line.