memory optimized tables - not supported inside user transactions

Mwaw

Hi getting the following error visual studio 2017 ReadyRoll Core project 

DDL statements ALTER, DROP and CREATE inside user transactions are not supported with memory optimized tables.

This is is the DDL being run

CREATE TABLE dbo.Test(

    Id      BIGINT      NOT NULL    IDENTITY(1,1) PRIMARY KEY NONCLUSTERED

) WITH (

    MEMORY_OPTIMIZED = ON

    , DURABILITY = SCHEMA_AND_DATA

);

--DROP TABLE IF EXISTS dbo.Test

C:\Program Files (x86)\VS2017_v1\MSBuild\15.0\Bin\Roslyn\csc.exe /noconfig /nowarn:1701,1702,2008 /nostdlib+ /errorreport:prompt /warn:4 /define:DEBUG;TRACE /errorendlocation /preferreduilang:en-US /highentropyva+ /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5.2\mscorlib.dll" /debug+ /debug:full /optimize- /out:obj\Debug\xxxx.dll /ruleset:"C:\Program Files (x86)\VS2017_v1\Team Tools\Static Analysis Tools\\Rule Sets\MinimumRecommendedRules.ruleset" /subsystemversion:6.00 /target:library /warnaserror- /utf8output "C:\Users\xxxx\AppData\Local\Temp\.NETFramework,Version=v4.5.2.SqlClrAttributes.cs"

Using ReadyRoll toolpath: C:\Program Files (x86)\VS2017_v1\MSBuild\ReadyRoll\ReadyRoll.Data.Schema.SSDT.targets

Using ReadyRoll version: 1.14.17.5347

It appears as if this was fixed https://documentation.red-gate.com/rr1/release-notes-and-other-versions/readyroll-1-14-release-notes

1.14.16 - September 27th, 2017

Fixes

• Disable transactions when Memory-Optimized Tables are present in the migration script

Please help, is there away of manually\signalling to disable the transaction when running?

David E

Hi,

You can disable the user transaction for the migration script by setting the following property at the top of the affected migration. For more details on how SQL Change Automation handles transactions see the article here.

<Migration ID="(migration id)" TransactionHandling="Custom" />

Out of interest how was the migration created? The fix mentioned above sets this property automatically if importing a MoT from the development database using the import screen. If the script was created manually or edited after import the Migration header will need to be set manually.

Mwaw

Thank you David.

I will change the header, and try again.

Yes - the table was created manually rather than importing from the DB.

Will look at the help pages a little more - where can I find the full list of properties found in the migration header xml fragment? (or is that it). I haven't come across any pages where we can tune the migration and influence the interaction with the "engine" - which will probably come in very handy!

David E

The only other header I'm aware of is the "InlineDependencies" option which is used as part of our CLR functionality to bring in external file contents.