Search code examples
sqlsql-serverdatabaseinner-join

SQL Server - Select Query - Inner Join - Error message: Cannot resolve the collation conflict

I'm performing this query in SQL Server:

SELECT *
FROM [PBS].[dbo].[CAT_Empleados]
INNER JOIN [AccessControl].[dbo].[USERINFO] ON [PBS].[dbo].[CAT_Empleados].[NumeroReloj] = [AccessControl].[dbo].[USERINFO].[Badgenumber]

As you can see, there are 2 databases, 2 tables, and 2 different columns.

In table CAT_Empleados, I have all the employees (around 3,000) - here the employee# column is NumeroReloj.

And in table USERINFO, I have around 250 employees - here the employee# column is Badgenumber.

What I'm trying to find is all employees who are in USERINFO and are not in CAT_EMPLEADOS (by the Employee#).

But I'm getting this error when I run the query:

Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Hope you can help me.

Solution

  • you can always use COLLATE at the end of your SELECT statement

    [PBS].[dbo].[CAT_Empleados].[NumeroReloj] = [AccessControl].[dbo].[USERINFO].[Badgenumber] COLLATE Chinese_PRC_CI_AS   (OR THE OTHER ONE)