I received the following error when I was attempting to compare columns in a SQL query between two tables located in two separate databases. In this case, the collation settings between the two databases were configured differently. Rather make any changes to the databases, I was able to apply a simple fix to my SQL query:

ORIGINAL QUERY –

UPDATE

[database1].[dbo].[table1]

SET

[id]=(SELECT[d2t1].[id]FROM[database2].[dbo].[table1][d2t1]WHERE[d2t1].[name] = [database1].[dbo].[table1].[name])

WHERE

[id] IS NULL

 

FIX –

Simply apply the default collation to the fields you are comparing.

UPDATE

[database1].[dbo].[table1]

SET

[id]=(SELECT[d2t1].[id]FROM[database2].[dbo].[table1][d2t1]WHERE[d2t1].[name]COLLATEDATABASE_DEFAULT = [database1].[dbo].[table1].[name]COLLATEDATABASE_DEFAULT)

WHERE

[id] IS NULL

Advertisements