How to use the COLLATE in a JOIN in SQL Server?
Asked Answered
P

2

43

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.

Prospect answered 2/9, 2016 at 21:30 Comment(0)
F
76

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 
Fahy answered 2/9, 2016 at 21:50 Comment(3)
With SQL Server the general "rule" appears to be string_expression COLLATE collation_name to treat the entity with a specific collation.. not sure how general it is, although it's reasonable enough for me to remember.Fascista
And how is this done in Linq ?Bret
@Jepsen possibly this answer may help #29723553Fahy
C
11

As a general rule, you can use Database_Default collation so you don't need to figure out which one to use. However, I strongly suggest reading Simons Liew's excellent article Understanding the COLLATE DATABASE_DEFAULT clause in SQL Server

SELECT *
  FROM [FAEB].[dbo].[ExportaComisiones] AS f
  JOIN [zCredifiel].[dbo].[optPerson] AS p
  ON (p.vTreasuryId = f.RFC) COLLATE Database_Default 
Cartridge answered 13/7, 2018 at 11:12 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.