SQL Collation conflict

While attempting to copy  records from older database backup

update a
set latitude = b.latitude, longitude = b.Longitude
from newtable a
inner join [OldDb].[dbo].[LegacyTable] b on
b.name = a.name

I’ve encoutered the following :

Msg 468, Level 16, State 9, Line 5
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

Possibly caused because the file comes from SQL Server 2008 while now I’m on the 2012. 

Luckly easy to fix by adding COLLATE DATABASE_DEFAULT on the columns used by the equals operator:


update a
set latitude = b.latitude, longitude = b.Longitude
from newtable a
inner join [OldDb].[dbo].[LegacyTable] b on
b.name = a.name
b.name COLLATE DATABASE_DEFAULT = a.name COLLATE DATABASE_DEFAULT




No Comments


You can leave the first : )



Leave a Reply

Your email address will not be published. Required fields are marked *