Collation Conflict when joining tables in different databases

Question

4.00/5 (1 vote)

See more:

Hello everyone, i have to perform a join between two tables which are on different databases. When working on the development machine everything was working fine until i deployed my database on the clients server. The Clients database is in a different collation...latin1_General_100_CI_AS.While my database is the default latin1_General_CI_AS.
This is the table in my database

SQL

Column Name                     Collation
QuestionGroupCombination	Latin1_General_CI_AS
QuestionGroup	Latin1_General_CI_AS
Company	Latin1_General_CI_AS
DepartmentCode	NULL
Position	Latin1_General_CI_AS
Weightage	NULL
UpdatedBy	Latin1_General_CI_AS

This is the table i want to perform the join on...which is on a different database

SQL

Code	Latin1_General_100_CI_AS
Name	Latin1_General_CI_AS
Dimension Code	Latin1_General_CI_AS

I want to perform join between the Departmentcode and Code columns between the two tables.
Here is my sql query

SQL

select distinct q.QuestionGroupCombination,q.QuestionGroup+'--'+u.Name+'--'+q.Position as CombinedExpr from QuestionGroup as q
                                         inner join [NAV-DB].[dbo].[vw_Department] as u on q.DepartmentCode = u.Code

This query throws the following error:

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

Also, a thing to note is that the code field in clients table is a varchar(20) type,while the DepartmentCode field is an int field in my table.

i have tried everything suggested in various posts...i tried changing my database collation...i changed the query on the '=' sign as Collate Latin1_General_100_CI_AS on both sides,only one side......then it gives me error saying expression int is invalid for collate clause....i have even changed the datatype of the Field in my table..etc
so, how can i resolve this issue??...Can anyone help...plz

EDIT: The DepartmentCode(varchar(20))...i changed the datatype and is no longer null...it shows Latin1_General_100_CI_AS...but still throws the above error... if it is possible i want to keep the datatype as int...since i have to change a lotta code
Thanks

Posted 17-Feb-13 5:46am

Minghang

Updated 17-Feb-13 5:49am

v2

Add a Solution

2 solutions

Solution 2

i am writting this here so that anyone looking for a solution to similar problem can later view it.
here is the code that saved me....Thanks to Zoltan Zorgo

SQL

select distinct q.QuestionGroupCombination,q.QuestionGroup+'--'+u.Name COLLATE DATABASE_DEFAULT+'--'+q.Position as CombinedExpr from QuestionGroup as q
                                         inner join [NAV-DB].[dbo].[vw_Department] as u on q.DepartmentCode COLLATE DATABASE_DEFAULT=u.Code COLLATE DATABASE_DEFAULT

The Link Provided above is also very useful...please view it.

Posted 17-Feb-13 6:33am

Minghang

Comments

RedDk 17-Feb-13 13:31pm

Look. This IS the above solution. Don't repeat it. What do you mean? It IS here already.

Minghang 18-Feb-13 10:46am

well, the whole point of this forum is to simplify things...and for a newbie like me a simple thing like this can take a little longer to understand...i am just trying to be helpful so that anyone who might face similar problem and who happens to be a newbie can understand it better

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Accepted Answer · 2013-02-17T05:58:00

Solution 1

Look around here: http://www.sqlusa.com/bestpractices2005/concatenationcollationconflict/[^]
The key point is, that you can not concatenate varchar fields from different collation, since (at least) the result collation would be undefined. So, you only need to figure out what resulting collation do you need as the concatenation result and change collation on select for at least one of them. This is also the case when you need to join on fileds with different collation: change collation in place.
Following statement should work:

SQL

select distinct 
q.QuestionGroupCombination,q.QuestionGroup+'--'+u.Name+'--'+q.Position as CombinedExpr 
from QuestionGroup as q
inner join [NAV-DB].[dbo].[vw_Department] as u on q.DepartmentCode COLLATE DATABASE_DEFAULT = u.Code COLLATE DATABASE_DEFAULT

Posted 17-Feb-13 5:58am

Zoltán Zörgő

Comments

Mehdi Gholam 17-Feb-13 12:01pm

5'ed

Minghang 17-Feb-13 12:09pm

thankyou zoltan for your quick reply...i already tried that...Its give the same error...Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

I even tried doing this...select distinct q.QuestionGroupCombination,q.QuestionGroup+'--'+u.Name+'--'+q.Position as CombinedExpr from QuestionGroup as q
inner join [NAV-DB].[dbo].[vw_Department] as u on q.DepartmentCode Collate Latin1_General_100_CI_AS=u.Code COLLATE Latin1_General_100_CI_AS...it is giving the same error

Zoltán Zörgő 17-Feb-13 12:15pm

Since it is complaining about "implicit" conversion, your problem seem to be in the concatenation, not (only) in the join. So, please read the first article, and add explicit collation to the fields involved in the concatenation.

To help you better, please script the whole DDL of these two tables, and paste here; so I can reproduce your situation.

Minghang 17-Feb-13 12:26pm

hey zoltan...it worked...thanks mate, Like you said the problem was actually in the concatination, the article you provided is going to be a gr8 help..you are a life saver...cheers