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

Question

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.

Answer 1

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 

Answer 2

USE master; GO ALTER DATABASE PRATAP COLLATE Latin1_General_CI_AS_KS_WS ; GO  --Verify the collation setting. SELECT name, collation_name FROM sys.databases WHERE name = N' PRATAP '; GO