Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

"Cannot resolve collation conflict for UNION operation"

Posted on 2007-11-20
3
Medium Priority
?
6,875 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 1500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

972 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