Symptoms:
When FNMS is working with Microsoft Server SQL Server 2019 version, they might receive the follow error message:
System.InvalidOperationException: Memory optimized tables and natively compiled modules cannot be accessed from within SQLCLR stored procedures.
Analysis:
SQL Server 2019 (15.x) introduces a new feature that's part of the in-memory database feature family: memory-optimized tempdb metadata. When this feature is enabled, the system tables involved in managing temporary table metadata can be moved into latch-free, non-durable, memory-optimized tables. However any memory optimized tables and natively compiled modules cannot be accessed from within SQL CLR stored procedure, and therefore causing compatible issue with FNMS.
User may see the error message listed above during some data activities such as during data warehouse export process.
Solution:
- When using SQL Server 2019, please ensure the cumulative update package 5 for SQL Server 2019 or later is installed.
- Disable TempDB_Metadata using any of the following commands and then restart the SQL Server.
--Option 1:
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF
GO
--Option 2:
EXEC sp_configure 'tempdb metadata memory-optimized', 0
GO
RECONFIGURE
GO
