Error: Cannot resolve collation conflict for equal to operation.

爱水的泥

A simple SQL statement:
SELECT distinct HoldNumber FROM HoldPart WHERE HoldNumber IN
(SELECT HoldNumber FROM [OPTION] .dbo.HoldPart)

Runtime prompt: Cannot resolve collation conflict for equal to operation.
I don't know why, there are masters!

我是杀人狂

SELECT * FROM HoldPart WHERE HoldNumber IN
(SELECT distinct HoldNumber FROM [OPTION] .dbo.HoldPart)

hiqwolfzzh

SELECT distinct HoldNumber
FROM HoldPart
WHERE HoldNumber IN
(SELECT distinct HoldNumber FROM [OPTION] .dbo.HoldPart)

Do not have duplicates in the result set of in

fanfan1980

--try
SELECT distinct HoldNumber FROM HoldPart WHERE HoldNumber IN
(SELECT distinct HoldNumber FROM [OPTION] .dbo.HoldPart)

nomic1077

It is because when the two tables are defined, the Collation of the HoldNumber field is different.

You can use sp_help to see what the Collation of the HoldNumber field in these two tables are ~
If they are not the same, there are two situations:
1. Change the Collation of the HoldNumber field in a table to make the Collation of the two tables the same
2.Add Collate conditions to explicitly specify Collation when querying