[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6877
  • Last Modified:

"Cannot resolve collation conflict for UNION operation"

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
DotTheBug
Asked:
DotTheBug
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
imitchieCommented:
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
 
DotTheBugAuthor Commented:
I'd figured out the answer before this comment came thru ..
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now