SQL Server Error 402

Error number

402

Severity

16 (user-correctable error)

State

1

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Database, Azure SQL Managed Instance

Category

Data Type & Conversion

Last reviewed

24 October 2026

What it means

Error 402 means an operator such as =, <> or + was given two data types it cannot work with together, typically a legacy text, ntext or image column, an xml value, or a uniqueidentifier joined to a string.

Every comparison and arithmetic operator has a list of type pairs it accepts. When the two operands fall outside that list, SQL Server stops the statement at compile time with 402 at severity 16. The message names both types in the order they appear in your expression and the operator in words: equal to for =, not equal to for <>, add for +. So text and varchar ... equal to means a text column on the left of = and a varchar literal on the right.

The usual culprits are the deprecated text, ntext and image types, which cannot be compared with =, and xml, which cannot be compared with a string at all. Error 402 differs from error 206 (no conversion between two types, for example in an assignment) and from error 8117, which the engine raises when one type is invalid for the operator on its own, such as image + image or date - date.

Common causes

• A WHERE or JOIN that compares a legacy text or ntext column with a string using = or <>.
• Building a message with + from a string and a uniqueidentifier, time or other type that + cannot combine with text.
• Comparing an xml column with a string literal instead of querying it with the xml methods.
• An image column compared with a varbinary value, for example to find duplicate files.
• Code written for varchar(max) running against an older copy of the table that still uses text or ntext.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name,
       OBJECT_NAME(c.object_id) AS table_name,
       c.name AS column_name, t.name AS data_type
FROM sys.columns AS c
JOIN sys.types AS t ON t.user_type_id = c.user_type_id
JOIN sys.tables AS tb ON tb.object_id = c.object_id
WHERE t.name IN (N'text', N'ntext', N'image', N'xml')
ORDER BY schema_name, table_name;

EXEC sp_describe_first_result_set
     N'SELECT status_note, CAST(status_note AS varchar(max)) AS as_varchar FROM dbo.Tickets;';

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

SELECT id
FROM dbo.Tickets
WHERE CAST(status_note AS varchar(max)) = 'urgent';

DECLARE @order_ref uniqueidentifier = NEWID();
SELECT CONCAT('Order ', @order_ref) AS label;

SELECT id
FROM dbo.Jobs
WHERE payload.exist('/job[@type="nightly"]') = 1;

ALTER TABLE dbo.Tickets ALTER COLUMN status_note varchar(max) NULL;

Examples

A legacy text column compared with =

CREATE TABLE dbo.Tickets (id INT PRIMARY KEY, status_note TEXT);

SELECT id
FROM dbo.Tickets
WHERE status_note = 'urgent';  -- text cannot be compared with =

Msg 402, Level 16, State 1, Line 3
The data types text and varchar are incompatible in the equal to operator.

A string joined to a uniqueidentifier with +

DECLARE @order_ref uniqueidentifier = NEWID();
SELECT 'Order ' + @order_ref AS label;

Msg 402, Level 16, State 1, Line 2
The data types varchar and uniqueidentifier are incompatible in the add operator.

An xml column compared with a string

CREATE TABLE dbo.Jobs (id INT PRIMARY KEY, payload XML);

SELECT id
FROM dbo.Jobs
WHERE payload = N'<job type="nightly"/>';

Msg 402, Level 16, State 1, Line 3
The data types xml and nvarchar are incompatible in the equal to operator.

How to prevent it

Retire text, ntext and image columns in favour of the max types, and do not create new ones: Microsoft has marked them deprecated for many releases. Store structured documents in xml or JSON only when you plan to query them with the matching functions, not by string comparison.

Use CONCAT or CONCAT_WS for every piece of display text that mixes types; it never trips over operator rules or NULLs. See SQL data types explained for picking modern types.

Common questions

Learn more on CodeWithSQL

• CONCAT()
• CAST()
• SQL data types explained

Official documentation

• ntext, text, and image (Transact-SQL)
• Comparison operators (Transact-SQL)
• exist() method (xml data type)
• Database Engine events and errors 0 to 999

Last technically reviewed 24 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.