Solved

"Cannot resolve collation conflict for UNION operation"

Posted on 2007-11-20
3
6,844 Views
Last Modified: 2012-06-27
Hi all!

How best to get around "Cannot resolve collation conflict for UNION operation." type error in the case of union syntax as per below?

select * from tbl1 inner join tbl 2
union
select * from anotherdb.tbl1 inner join anotherdb.tbl2

Thank you!
0
Comment
Question by:DotTheBug
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20323664
that means that the columns from the first query do not have the same collation from the second query.

you need to adjust the collation in either query to get matching values.

see this article:
http://www.databasejournal.com/features/mssql/article.php/1587631

note:
please avoid SELECT * in your queries whenever possible. it will only lead to problems

also, use UNION ALL instead of UNION, as union alone will perform an implicit DISTINCT over the resulting set.

0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20323827
select * from tbl1 inner join tbl 2
union
select * from anotherdb.tbl1 inner join anotherdb.tbl2

for all CHar, varchar, nvarchar fields (strings), add "collate <collation>"

select f1, f2, f3 collate database_default, f4 collate database_default
from tbl1 inner join tbl 2
union
select f1, f2, f3 collate database_default, f4 collate database_default
from anotherdb.tbl1 inner join anotherdb.tbl2
0
 

Author Closing Comment

by:DotTheBug
ID: 31410237
I'd figured out the answer before this comment came thru ..
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question