Cannot resolve the collation conflict between “Modern_Spanish_CI_AS” and SQL_Latin1_General_CP1_CI_AS” in the equal To operation.

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:

Select *

From Tabla

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

INSERT ……

VALUES….

WHEN MATCHED THEN

UPDATE SET

T.Con=S.Con,

…….

 

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.