Back to Top
  3. Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

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

Posted on Wednesday, 3 May 2017
Problem :-- How to  resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in Sql Server.

Introduction: Sql Server is the Back end Coding area where we can save the data related. Therefore collate is the general clause that is used in the exceution of the SQL Queries.
Collate clause is used :--

  • To resolve the problem where the data type of field is different and we want to apply the conditions on them.
  • Collate is used when we apply the operators on the fields with different Data Types.
If there occurs a problem like following :---



  •    Msg 468, Level 16, State 9, Line 1  Cannot resolve the collation conflict between   "Latin1_General_CI_AI" and  "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

This shows that during the operation in two fields or columns there is a difference in the language or data type in which they have been defined,than n such cases we need to use collate clause in Sql Server Queries

 Above is containing two types of tatement for example :--

  • Latin1_General_CI_AI
  • SQL_Latin1_General_CP1_CI_AS
These shows that the two different fields on which we want to apply the operations are in different Type and we will have to select them by collating them either to one type  which the Other one is holding.



Steps  to resolve this problem:---

  • First of all check the query where the error is coming.
  • It need to use the collate clause if we are getting the above error.
  • Collate can be used during create or alter table. 
  • It is basically used for a character string .

Demo:--
  1. SELECT *   FROM Table1 where fieldName not in  (SELECT  fieldName  Collate Latin1_General_CI_AI   FROM Table2 )                                                                                                                                                                                                                                                                   
  2. SELECT  fieldName  Collate Latin1_General_CI_AI   FROM Table2
Collate is the keyword that is used in the sql server to solve the issues like Data Type difference in the fields.






at