Applications

SQL SERVER: Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation

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

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.