Sometimes when you are working with different database to get data the way you require, you can get the next error:
Msg 468, Level 16, State 9, Line 3: “Cannot resolve the collation conflict between “Modern_Spanish_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.”
It happens because the database collations of those two database I was working on have different collation as show the next two pictures:
To resolve the problem you have to identify the column which are presenting the problem. Normally you can identify them in two cases:
- If you are working with a simple query like:
Where Cond1 = Cond2
The “issue” is going to be in the data you are using in the clause Where.
- If you are working with a Merge sentence like:
MERGE PISGRSTG_Dev.dbo.Fuente AS T
USING PISGR_Dev.dbo.Fuente AS S
ON T.IdTipoRecurso=S.IdTipoRecurso AND T.IdTipoEntidad=S.IdTipoEntidad AND T.IdEntidad=S.IdEntidad
WHEN NOT MATCHED BY TARGET THEN
WHEN MATCHED THEN
The “issue” is going to be in the data you are using in the clause On.
As I was using a Merge statement in this case I fixed the error as show below:
ON T.IdTipoRecurso=S.IdTipoRecurso AND
T.IdTipoEntidad=S.IdTipoEntidad AND T.IdEntidad=S.IdEntidad COLLATE Modern_Spanish_CI_AS
Is Like make a cast between two database collations.