Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AI” in the equal to operation

sql-server-2008

I am getting below said collation error since I have moved from a desktop machine to a laptop. My database has hundreds of stored procedures, so any solution like overriding some queries or fix collation for a column is not possible for me.

"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation"

My problem is not unique and I have searched for this quite a lot, but solutions available suggest me to override queries with some code which is not feasible. Please suggest some solution that may avoid this collation thing.

I have tried this to alter my database collation.

ALTER DATABASE testDB  
COLLATE French_CI_AI ;  
GO

Thanks.

Best Answer

Just use the following syntax to collate on the fly when joining tables with different collations. I integrate systems, so I have to do this a lot.

select * from [Product] p join [category] c 
on  
c.[Name] collate SQL_Latin1_General_CP1_CI_AS 
= 
p.[Name] collate SQL_Latin1_General_CP1_CI_AS
Related Topic