Solved

"Cannot resolve collation conflict for UNION operation"

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 32
Find SQL query used by application 3 20
Find results from sql within a time span 11 33
SQL - Copy data from one database to another 6 20
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

832 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