This bit me (again) today. It had been so long since the last time that I had to google for help.
Problem: Select colA, colB, colC from tableA UNION Select colA, colB, colC from tableB when one of the columns is an ntext will result in this confusing error:
“The text, ntext, or image data type cannot be selected as DISTINCT.”
Thanks to this Wrox forum thread, I got a quick lesson of why I get an error regarding a clause that I’m not even using (DISTINCT) and a reminder to use UNION ALL instead of just UNION.
In case that thread ever disappears I will paste the key info from Jeff Mason.
The UNION operator by default eliminates duplicate rows from the resultset. The duplicate eliminating operation is similar to a SELECT DISTINCT. You can’t use SELECT DISTINCT on a column that is text type.
Try UNION ALL which will not remove duplicates, or remove the text column(s).
Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!