It’s a migrated SQL 2000 product in 8.0 Compatibility mode!
I cannot use equals (=) in a WHERE clause against the text column
So when I want to write
SELECT * FROM ProductUpdates WHERE Build = ‘9.71’I get ...
Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the equal to operator.
I have to use PATINDEX to get around it, like this –
SELECT * FROM ProductUpdates WHERE PATINDEX('9.71',Build) > 0Or CAST the column like this
SELECT * FROM ProductUpdates WHERE CAST(Build AS VARCHAR(MAX)) = ‘9.71’
Although this second approach would ruin the sargability of the query, i.e. the ability to use an index seek on the column.
Link : Using equal operator in transact-SQL for ntext datatype column