Menu
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.
249
Issue: Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” Simply apply the default collation to the fields you are comparing.
error (Cannot resolve the collation conflict between .... ) usually occurs while comparing data from multiple databases. since you cannot change the collation of databases now, use COLLATE DATABASE_DEFAULT.
The collate clause is used for case sensitive and case insensitive searches in the columns of the SQL server. There are two types of collate clause present: SQL_Latin1_General_CP1_CS_AS for case sensitive. SQL_Latin1_General_CP1_CI_AS for case insensitive.
You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.
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 
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
