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

by

Why This Error Happens

Error 4154 occurs when the SQL Server 2025 query parser encounters the UNNEST operator applied to a value that the engine cannot resolve as a native MULTISET collection type. This typically happens when a JSON array, a table-valued variable, or an OPENJSON result set is passed into UNNEST without being explicitly cast, or when the database is running in a compatibility level that does not fully support the new ANSI SQL:2023 multiset extensions introduced in SQL Server 2025. In rare cases, a corrupted or outdated cached execution plan can also cause the engine to misidentify the data type being passed to UNNEST.

Step-by-Step Resolution Guide

Step 1: Verify Database Compatibility Level

  • Open SQL Server Management Studio (SSMS) and connect to the instance.
  • Run the following query to check the current compatibility level:
  SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
  • If the level is below 170 (SQL Server 2025), update it:
  ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 170;

Step 2: Correct the Query Syntax for Multiset Casting

  • Ensure any JSON array or table expression passed to UNNEST is explicitly cast:
  SELECT * FROM UNNEST(CAST(@jsonArray AS MULTISET));
  • If using OPENJSON, confirm it returns a multiset-compatible structure rather than a raw rowset:
  SELECT value FROM OPENJSON(@json) WITH (value NVARCHAR(MAX) '$');
  • Avoid passing scalar variables or non-collection types directly into UNNEST.

Step 3: Update SQL Server Drivers and Client Tools

  • On Windows Server 2025 / Windows 11, confirm you are using the ODBC Driver 18+ or OLE DB Driver 19+ for SQL Server, as older drivers do not recognize the new multiset type.
  • Download the latest driver from the official Microsoft Download Center and reinstall.
  • Restart the SQL Server (MSSQLSERVER) service after installation via services.msc.

Step 4: Clear and Rebuild the Execution Plan Cache

  • Corrupted cached plans can cause type-resolution errors. Clear the plan cache for the affected database:
  DBCC FREEPROCCACHE;
  • Re-run the query to force SQL Server to generate a fresh execution plan with correct type inference.

Step 5: Apply the Latest Cumulative Update (CU)

  • Check Windows Update or the SQL Server Installation Center for the latest Cumulative Update for SQL Server 2025.
  • Install the update, as early CUs for 2025 contained known parser bugs related to ANSI multiset handling in UNNEST.

Alternative or Verification Steps

  • After applying the fix, run the query in SSMS with Actual Execution Plan enabled — the plan should show a valid Table Valued Function [UNNEST] operator without type-conversion warnings.
  • If the error persists, isolate the issue by testing the UNNEST expression independently in a new session using CAST(... AS MULTISET) to confirm whether the root cause is syntax-based or driver/version-based.