MS SQL 2005 and ntext

[cheftimo]

I have a simple SELECT statement:
SELECT RestaurantID, Status, RestaurantName, City, PostalCode, EmailAddress,
URL
FROM tblRestaurants
WHERE URL <> ''
It works with an Access mdb but not with MS SQL 2005. I get an error "varchar
and ntext data types are incompatible with the <> (NEQ) operator". Of the
above, the only ntext colum is URL; when I remove it, the query works fine,
even though other colums are type varchar. Any ideas?

[PaulH *ACE*]

cheftimo wrote:

> I have a simple SELECT statement:
> SELECT RestaurantID, Status, RestaurantName, City, PostalCode, EmailAddress,
> URL
> FROM tblRestaurants
> WHERE URL <> ''

can the URL column be NULL & is that the logic you're after? if so, try

WHERE URL IS NOT NULL

[philh]

The only valid comparator for text and ntext fields is the LIKE function. These fields can be up to 2GB in length, so you could imagine the performance hit for a complete comparison of values.

[PaulH *ACE*]

philh wrote:

> The only valid comparator for text and ntext fields is the LIKE function.
> These fields can be up to 2GB in length, so you could imagine the performance
> hit for a complete comparison of values.

IS NULL/IS NOT NULL works on ntext/text ok. but i guess that raises the question
of why use ntext for a column holding URLs? i would guess nvarchar(1000) would
be more than enough.