Solved

"Cannot resolve collation conflict for UNION operation"

Posted on 2007-11-20
3
6,834 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 142

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now