Issue – Customer was getting below error when he was running any query.

Msg 468, Level 16, State 9, Line 36
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “SQL_Latin1_General_CP1_CS_AS” in the equal to operation.

Back ground – Table was part of replication and when he was running query on subscribe he was getting collation conflict error.

Troubleshooting –

Upon checking; I found both the tables were having different collation by using sp help <tablename> query.

Resolution –

1. use option COLLATE DATABASE_DEFAULT on join column which having different collation setting

e.g.

select * from text1 t1 join t2 on t.id = t.id

let’s assume text1.id is SQL_Latin1_General_CP1_CS_AS and text2.id is SQL_Latin1_General_CP1_CI_AS and database collation is SQL_Latin1_General_CP1_CS_AS

so you can rewrite the query –

select * from text1 t1 join t2 on t.id = t.id COLLATE DATABASE_DEFAULT

COLLATE DATABASE_DEFAULT override the table level\column level collation and allow you to run your query.

1. change collation of all the column in destination table ( in my case issue was with subscriber table )

you can use below query to script alter command ( courtesy https://stackoverflow.com/questions/18122773/change-collations-of-all-columns-of-all-tables-in-sql-server ) and run the alter command.

DECLARE @collate nvarchar(100);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);

SET @collate = ‘SQL_Latin1_General_CP1_CS_AS’;

DECLARE local_table_cursor CURSOR FOR

SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N’IsUserTable’) = 1 and name In (‘text1’, ‘text2’)

OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE local_change_cursor CURSOR FOR

SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
, c.name column_name
, t.Name data_type
, c.max_length
, c.column_id
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID(@table)
ORDER BY c.column_id

OPEN local_change_cursor
FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id

WHILE @@FETCH_STATUS = 0
BEGIN

IF (@max_length = -1) OR (@max_length > 4000) SET @max_length = 4000;

IF (@data_type LIKE ‘%char%’)
BEGIN TRY
SET @sql = ‘ALTER TABLE ‘ + @table + ‘ ALTER COLUMN ‘ + @column_name + ‘ ‘ + @data_type + ‘(‘ + CAST(@max_length AS nvarchar(100)) + ‘) COLLATE ‘ + @collate
PRINT @sql
EXEC sp_executesql @sql
END TRY
BEGIN CATCH
PRINT ‘ERROR: Some index or constraint rely on the column’ + @column_name + ‘. No conversion possible.’
PRINT @sql
END CATCH

FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id

END

CLOSE local_change_cursor
DEALLOCATE local_change_cursor

FETCH NEXT FROM local_table_cursor
INTO @table

END

CLOSE local_table_cursor
DEALLOCATE local_table_cursor

GO

e.g.

ALTER TABLE text1 ALTER COLUMN id char(1) COLLATE SQL_Latin1_General_CP1_CS_AS

ALTER TABLE text2 ALTER COLUMN id char(1) COLLATE SQL_Latin1_General_CP1_CS_AS