Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server
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
 
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
 
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
 
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 6:46am
Edited 17-Feb-13 6:49am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

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:
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
  Permalink  
Comments
Mehdi Gholam at 17-Feb-13 12:01pm
   
5'ed
Minghang at 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ő at 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 at 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
Rate this: bad
good
Please Sign up or sign in to vote.

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
 
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.
  Permalink  
Comments
RedDK at 17-Feb-13 13:31pm
   
Look. This IS the above solution. Don't repeat it. What do you mean? It IS here already.
Minghang at 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

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

  Print Answers RSS
0 OriginalGriff 250
1 Jochen Arndt 155
2 PIEBALDconsult 150
3 DamithSL 125
4 Afzaal Ahmad Zeeshan 120
0 OriginalGriff 5,695
1 DamithSL 4,591
2 Maciej Los 4,012
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,190


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 17 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100