how we can select two columns having different collation

Question

I have an SQL query like

SELECT Col1, Col2 FROM Table1
UNION ALL 
SELECT Col1, Col2 FROM Table2

where col1 and col2 are strings and using collations.

When I run the query it shows the error:

1. Cannot resolve collation conflict for column 1 in statement.
2. Cannot resolve collation conflict for column 2 in statement.

Any one please help.

Answer 1

Is the error a difference in case sensitivity between the two tables? That is the error that I have most often seen.
If so, collate the offending table back to good old Latin1_General_CI_AS or whatever else is most appropriate.

For example, if Table1 was case sensitive and you want to collate both tables as if they were case insensitive:

SELECT Col1 COLLATE Latin1_General_CI_AS, 
       Col2 COLLATE Latin1_General_CI_AS FROM Table1
UNION ALL 
SELECT Col1, Col2 FROM Table2