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

by

Troubleshooting Guide: SQL Server Error 5335 — “The data type %ls cannot be used as an operand to the UNION”

Why This Error Happens

This error occurs when a UNION or UNION ALL statement attempts to combine columns that include incompatible or unsupported large object (LOB) data types, such as text, ntext, image, xml, geography, geometry, or CLR user-defined types, without proper conversion. SQL Server’s query engine cannot implicitly compare or merge these data types across result sets because they lack a defined sort or equality operator required for the DISTINCT-style processing that UNION performs internally. This typically surfaces after a schema change, a migration from an older SQL Server version, or when a query was written against columns that were later altered to deprecated or large-object types.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Data Type

  • Run the failing query in SQL Server Management Studio (SSMS) and note the exact error message, which specifies the problematic data type (e.g., text, xml, geography).
  • Use sp_help 'YourTableName' or query sys.columns joined with sys.types to confirm which column(s) in your SELECT statements use the flagged type.
  • Check both sides of the UNION to ensure column order and types align correctly.

Step 2: Explicitly Cast or Convert the Column

  • Modify the query to cast the problematic column to a comparable type, such as NVARCHAR(MAX) for text/ntext/xml, or VARBINARY(MAX) for image.
  • Example fix:
SELECT CAST(MyXmlColumn AS NVARCHAR(MAX)) FROM TableA
UNION
SELECT CAST(MyXmlColumn AS NVARCHAR(MAX)) FROM TableB;
  • For spatial types (geography/geometry), convert using .STAsText() or .ToString() before applying UNION.

Step 3: Replace UNION with UNION ALL Where Duplicates Are Acceptable

  • If the deduplication behavior of UNION is not required, switch to UNION ALL, which does not attempt to sort/compare rows and may bypass the type restriction for certain scenarios.
  • Validate this doesn’t introduce duplicate records that break downstream reporting or application logic.

Step 4: Update Application or ORM-Generated Queries

  • If the query is auto-generated by an ORM (Entity Framework, Dapper, etc.), check the model/entity definitions for columns mapped to deprecated types.
  • Update the data access layer to explicitly handle type conversion before the UNION operation is constructed.

Step 5: Audit Table Schema for Deprecated Types

  • On Windows Server 2025 / Windows 11 hosts running SQL Server 2025, open SSMS and run sys.dm_db_uncontained_entities or a custom script to flag all text, ntext, and image columns still in use, since these are deprecated and should be migrated to varchar(max), nvarchar(max), or varbinary(max) long-term.

Alternative or Verification Steps

  • Re-run the original query after applying the CAST/CONVERT fix and confirm Error 5335 no longer appears in the SSMS results pane or the SQL Server Error Log (Application and Services Logs > SQL Server in Windows Event Viewer).
  • If the error persists, isolate each SELECT statement individually (without the UNION) to confirm each runs cleanly, then incrementally reintroduce the UNION to pinpoint exactly which column pairing still triggers the type mismatch.