Menu
When i issue a SQL query, sometimes I get the following error message:
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
I often solve this problem just make both table collation same. so i need to know is there any quick way to fix this issue.
I'd like to put something special in my SQL query as a result if collation is not same for both table in sql query then also query will work perfectly without any error. is there any solution?
773
Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types, such as char and varchar, dictate the code page and corresponding characters that can be represented for that data type.
In Microsoft SQL Server, the collation can be set at the column level. When you compare (or concatenate) two columns having different collation in a query, this error occurs: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "French_CI_AS" in the equal to operation.
The server collation is specified during SQL Server installation. The default server-level collation is based upon the locale of the operating system. For example, the default collation for systems using US English (en-US) is SQL_Latin1_General_CP1_CI_AS.
Alternatively, if the database already exists, right-click the database that you want and select Properties. Select the Options page, and select a collation from the Collation drop-down list. After you are finished, select OK.
You can force which collation by using the COLLATE clause.
i.e.
SELECT *
FROM Table1 T1
INNER JOIN Server2.dbo.Table2 T2
ON T1.Name = T2.Name COLLATE database_default
Collation conflicts are common when joining tables between two databases or servers, especially if the version of the DB is different.
114
You can specify a collation in a query using the collate clause:
where col1 = col2 collate Latin1_General_CI_AS
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
