sql-serverjoincollate

How to use the COLLATE in a JOIN in SQL Server?

I´m trying to join two tables but I get this error:

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

This is the code I´m using:

 SELECT *

  FROM [FAEB].[dbo].[ExportaComisiones] AS f

  JOIN [zCredifiel].[dbo].[optPerson] AS p

  ON (p.vTreasuryId = f.RFC) COLLATE Latin1_General_CI_AS

I know it is wrong, it underlines COLLATE. I do not know how to apply it.

Solution

  • Correct syntax looks like this. See MSDN.

    SELECT *
  FROM [FAEB].[dbo].[ExportaComisiones] AS f
  JOIN [zCredifiel].[dbo].[optPerson] AS p

  ON p.vTreasuryId COLLATE Latin1_General_CI_AS = f.RFC COLLATE Latin1_General_CI_AS