Error - Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CS_AS" in the equal to operation.RSS

• • Reply
  JagjitSingh

  Member

  504 Points

  1777 Posts

  Error - Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_...

  Nov 26, 2018 06:35 AM|JagjitSingh|LINK

  Hi

   I get above error when i add below Bolded line

  select * FROM [Test0] AS t1 
  WHERE t1.[No_] not in (Select EmployeeID from dbo.Emp) 

  Thanks

• • Reply
  PatriceSc

  All-Star

  43500 Points

  15164 Posts

  Re: Error - Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Lat...

  Nov 26, 2018 10:06 AM|PatriceSc|LINK

  Hi,

  As a quick fix you could add a COLLATE clause. See https://docs.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-2017

  select * FROM [Test0] AS t1 
  WHERE t1.[No_] not in (Select EmployeeID COLLATE Latin1_General_CP1_CI_AS from dbo.Emp)  -- Or maybe the other way round ?

  Then you'll likely want to check your db and check you are consistent in  how https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017 is handled.

  In short you can define how character values are sorted and compared at the server, db, table and column level. More likely you should try to be consistent at least at the db level.

  Edit: you could try the following to see how may columns you have for each collation :

  SELECT COLLATION_NAME,COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS AS C
  JOIN INFORMATION_SCHEMA.TABLES AS T ON T.TABLE_SCHEMA=C.TABLE_SCHEMA AND T.TABLE_NAME=C.TABLE_NAME
  WHERE TABLE_TYPE='BASE TABLE'
  AND COLLATION_NAME IS NOT NULL
  GROUP BY COLLATION_NAME