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