Wednesday, February 4, 2009

Error: The data types text and varchar are incompatible in the not equal to operator.

Error: The data types text and varchar are incompatible in the not equal to operator.

From books online, index entry Boolean expressions:

"Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types. The following table lists the Transact-SQL comparison operators."

= (Equals)
> (Greater Than)
< (Less Than)
>= (Greater Than or Equal To)
<= (Less Than or Equal To)
<> (Not Equal To)
!= (Not Equal To)
!< (Not Less Than)
!> (Not Greater Than)


If your description are always (or even usually) under 8000 characters, you need to change to a varchar type. Or you might want to implement two columns, one a smaller description, the other a text column that has long values.

for text column, change:

where field2 != ''


to:

where datalength(field2)

No comments: