Collation Error Issue (1 Viewer)

[Zedster]

I have a recently created SQL database for actions called "Actions", there is already an existing database on the same server for "Employees".

I wish to pull the Employee name via their staff number into the actions database (every action has a "By_Who" field of same data type as the staff number)

I have created a view in the actions database called V_Employees it lists each employee name along with StaffNo. It runs fine.

When I try to create an inner join with tblActions in the "Actions" database I get the following error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in the equal to operation.

When I checkout the collation between the two tables (on different databases) I get the following:

In tblActions: PS_ByWho = SQL_Latin1_General_CP1_CS_AS
In V_Employees: StaffNo = SQL_Latin1_General_CP1_CI_AS

I can see what the problem is but I am not very ofay with collations, what is the best way to resolve it (I would be interested to know how it came about also if possible). I created both databases, one a while ago, one more recent.

 

[Zedster]

I have just spotted that one table uses varchar(10) data type, the other nvarchar(10). Which I am guessing is the problem.

I assume though if I change the datatype on one of the tables it will then create a similar problem with all other relations to that field in the same database?

Interestingly I have been joining on these two fields for a long time without problem in MS Access, but I am trying to slowly move my databases to run passthrough queries rather than local queries.

 

[Isaac]

this is a quick n dirty solution and not optimized at all, but what is you join on cast(fieldname as varchar(10))

 

[Zedster]

Thanks for your suggestion, I have managed to solve it. It wasn't anything to do with nvarchar vs varchar it was to do with the "Employees database" had been set up in US-English and the "Actions database" set up in British English, this caused the join problem. I solved it by changing the collation on the "Actions" database to US-English

 

[Isaac]

Glad to hear it's working.