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.

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 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.