How to fix Error 367 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 367 – “The hint ‘%.*ls’ is valid only with memory optimized tables”

Why This Error Happens

This error occurs when a Transact-SQL query applies a table hint—such as WITH (SNAPSHOT)—that is exclusively designed for memory-optimized (In-Memory OLTP) tables, but the query targets a standard disk-based table instead. SQL Server 2025 validates hint compatibility at parse time, and since disk-based tables do not use the Hekaton storage engine, the hint has no valid execution path. This typically results from copy-pasted query logic, migrated stored procedures, or ORM-generated SQL that assumes a memory-optimized schema that doesn’t actually exist on the target table.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Table

  • Capture the exact query text from the error message or SQL Server error log (Event Viewer > Windows Logs > Application, filter by Error 367).
  • Run sp_helptext or check the stored procedure/view definition referencing the hint.
  • Confirm the table’s storage type by running:
  SELECT name, is_memory_optimized FROM sys.tables WHERE name = 'YourTableName';

Step 2: Remove or Correct the Invalid Hint

  • If the table is disk-based, remove the memory-optimized-only hint (e.g., WITH (SNAPSHOT), WITH (READ_COMMITTED_SNAPSHOT) when used in this context) from the query.
  • Example fix:
  -- Before (invalid on disk-based table)
  SELECT * FROM Orders WITH (SNAPSHOT) WHERE OrderID = 100;

  -- After (corrected)
  SELECT * FROM Orders WHERE OrderID = 100;
  • If isolation-level control is still needed, use standard hints like WITH (NOLOCK) or WITH (READUNCOMMITTED) instead.

Step 3: Convert the Table to Memory-Optimized (If Intentional)

  • If the hint usage was intentional and you require memory-optimized behavior, convert the table using SQL Server Management Studio (SSMS) or T-SQL:
  ALTER TABLE Orders
  ADD MEMORY_OPTIMIZED = ON;
  • Ensure the database has a MEMORY_OPTIMIZED_DATA filegroup configured:
  ALTER DATABASE YourDatabase 
  ADD FILEGROUP YourDB_mod CONTAINS MEMORY_OPTIMIZED_DATA;
  • Verify sufficient RAM allocation and that the SQL Server 2025 instance supports In-Memory OLTP (Enterprise/Standard edition dependent).

Step 4: Update Application or ORM Layer

  • Check Entity Framework, Dapper, or custom data-access layers for hardcoded hints applied generically across all tables.
  • Refactor query-generation logic to apply memory-optimized hints only to tables flagged as is_memory_optimized = 1.

Alternative or Verification Steps

  • Re-execute the query in SSMS after removing the hint and confirm no Error 367 appears in the Messages tab.
  • Run sys.dm_db_stats_properties and check sys.tables.is_memory_optimized post-migration to confirm the table type matches the hints being used in production code.