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
Member
504 Points
1776 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
All-Star
48570 Points
18082 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 :