;)
Issue: Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS”
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] COLLATE DATABASE_DEFAULT = [database1].[dbo].[table1].[name] COLLATE DATABASE_DEFAULT)
WHERE
[id] IS NULL
