Error/Fix: Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation
Recently I was working on a SQL query in my inventory database, and was greeted with below error message:-
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
Resolution:
To resolve the issue, first let’s we have to find exact collation of the columns involved. Below is the query for same.
1 2 3 4 5 6 |
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE c.TABLE_SCHEMA = 'dbo' AND ( c.TABLE_NAME = 'Instance' AND c.COLUMN_NAME in ('Name') ) OR ( c.TABLE_NAME = 'InstanceList' AND c.COLUMN_NAME in ('ServerName') ) GO |
As we can see in above screenshot, Collation for column dbo.InstanceList.ServerName is Latin1_General_CI_AS compared to SQL_Latin1_General_CP1_CI_AS of column dbo.Instance.Name.
In order to make the query work, we need to use hint COLLATE <Collation of Other Comparing Column> in inner query. Below is the final query as shown in screenshot.
Friends, please feel free to correct me by comments. If you like the article, do Like & Share. Happy Coding 🙂
Related
Related Posts
-
Query Optimization 03 – Users statistics (individual user) [StackOverflow]
No Comments | Nov 27, 2016
-
Query Shorcuts SQL Server SSMS – Get Max Data Length
No Comments | Sep 30, 2016
-
Easy Trick to Debug Long TSQL Codes
No Comments | May 6, 2017
-
Error/Fix: The database owner SID recorded in the master database differs from the database owner SID recorded in database
No Comments | Jan 28, 2017
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.