Error msg - The text, ntext, or image data type cannot be selected as DISTINCT

Problem :

SELECT id,remarks from table1
UNION
SELECT id,remarks from table2
note: the REMARKS field type is TEXT

I want to query the distinct records so i dont want to use the UNION ALL but I'm receiving the error msg below

Server: Msg 8163, Level 16, State 4, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.

Solution:

1) give up
If you can’t change data type and your data length are likely more than 4000, give up.

2) using convert function
using convert in select statement, but this will omitted the data behind length 4000. eg. select convert( nvarchar(4000), fieldname ) as alias from …

3) change data type
change data type from ntext to nvarchar(4000).

SELECT id,cast(remarks as nvarchar(400)) from table1

UNION

SELECT id,cast(remarks as nvarchar(400)) from table2

More Information: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=530757&SiteID=1