:::: MENU ::::

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

  • Aug 26 / 2014
  • 0
Data Modeling and Database Design, dbDigger, SQL Server Collations

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

Today one of our development team member reported following error on newly restored database in staging environment.

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

Error was generated in a stored procedure. First of all i found that collation of subject database is different than SQL_Latin1_General_CP1_CI_AS. Using this method i changed the collation of subject database to required SQL_Latin1_General_CP1_CI_AS.
I was hopeful that changing collation of database will solve the issue but it was not the case. I opened the stored procedure code and got the tables being used in it.
Suppose we have table1, table2 and table3 in USP. Now we have to get all those columns where collation is different than our required collation. Following script did task and returned the columns with different collation.

SELECT object_name(object_id) as TableName,   
 name as columnName, collation_name  
 FROM sys.columns  
 WHERE OBJECT_ID IN (SELECT OBJECT_ID  
 FROM sys.objects where name in ('table1','table2','table3'))  
 and collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
GO

Once we have the list of columns then we can change their collation to match with required collation. This can be done with following script.

ALTER TABLE table1 ALTER COLUMN ColumnNameHere nvarchar(512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL  
GO
ALTER TABLE table2 ALTER COLUMN ColumnNameHere varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL  
GO
ALTER TABLE table3 ALTER COLUMN ColumnNameHere varchar(512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
GO

This time i found that issue was resolved and there was no error.

Consult us to explore the Databases. Contact us