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).

www.acehaid.org

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!