Olá pessoal,
Boa tarde!
Neste post vou demonstrar a vocês como resolver de forma simples e rápida um problema que apesar de ser simples e a mensagem ser bem clara, já vi muitos analistas não sabendo como resolver.
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AI” and “SQL_Latin1_General_CP1_CS_AS” in the equal to operation.
O que é uma COLLATION? Simulando o erroSimulando o erro
Uma forma fácil de simular esse erro é criando uma tabela onde duas ou mais colunas possuem collations diferentes e tentar fazer uma comparação no WHERE entre essas colunas, uma concatenação ou criar duas tabelas com colunas de collations diferentes e tentar realizar um join entre as 2 colunas.
Vale lembrar que esse erro só acontece ao comparar ou manipular duas colunas de TEXTO (VARCHAR, NVARCHAR, CHAR, etc) de collations diferentes.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
IF (OBJECT_ID('tempdb..#Teste1') IS NOT NULL) DROP TABLE #Teste1 CREATE TABLE #Teste1 ( Id INT IDENTITY(1,1), Nome1 VARCHAR(100), Nome2 VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS ) INSERT INTO #Teste1 SELECT 'SQL Server', 'Oracle' SELECT A.Nome2 + ' é melhor que ' + A.Nome1 FROM #Teste1 A |
Realizando a consulta sem alterar estrutura
Uma forma simples, rápida e prática para conseguir contornar esse problema de forma provisória ou quando você não tem acesso para alterar a estrutura da tabela/database, é utilizar o operador COLLATE no próprio select, que irá converter todos os dados da coluna para um determinado collation e depois poderá comparar e trabalhar com os dados normalmente.
|
1 2 |
SELECT A.Nome1 COLLATE SQL_Latin1_General_CP1_CS_AS + ' é melhor que ' + A.Nome2 FROM #Teste1 A |
Você ainda pode utilizar o collation DATABASE_DEFAULT nas duas colunas envolvidas, para garantir a compatibilidade entre elas, convertendo as duas colunas para a mesma collation do banco:
|
1 2 3 |
SELECT * FROM #Teste1 A LEFT JOIN #Teste1 B ON A.Nome1 COLLATE DATABASE_DEFAULT = B.Nome2 COLLATE DATABASE_DEFAULT |
Essa é uma solução provisória e de contorno, que não deve ser aplicada em grandes volumes de dados, uma vez que a performance não é ideal, já que toda a coluna precisa ser lida e convertida para depois trabalhar com o dado.
Como identificar o COLLATION do database
O primeiro passo ao identificar um problema de conflito de collation entre databases, é identificar primeiro qual o collation dos databases envolvidos para tentar entender se o problema está neste posto. Para se identificar o collation de um database, podemos utilizar o comando sp_helpdb:
Também podemos utilizar a view de catálogo sys.databases:
|
1 2 3 |
SELECT name, collation_name, compatibility_level FROM sys.databases WHERE name IN ('master', 'msdb', 'model', 'tempdb') |
E também utilizar a função DATABASEPROPERTYEX:
|
1 |
SELECT DATABASEPROPERTYEX('master', 'Collation') |
Como identificar o COLLATION de uma coluna
Após analisar o collation dos databases envolvidos e constatar que já estão utilizando a mesma codificação, vamos analisar agora as colunas envolvidas.
Existem várias formas de realizar essa verificação, como a sys.columns:
|
1 2 3 4 |
SELECT A.name, A.column_id, B.name, A.max_length, A.[precision], A.scale, A.collation_name FROM msdb.sys.columns A JOIN msdb.sys.types B ON A.user_type_id = B.user_type_id WHERE [object_id] = OBJECT_ID('msdb.dbo.sysjobs') |
Também podemos obter essa informação analisando as views do database INFORMATION_SCHEMA:
|
1 2 3 |
SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE, COLLATION_NAME FROM msdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'sysjobs' |
And that's it, folks!
Espero que tenham gostado do post!
Qualquer dúvida, é só deixar seu comentário
Abraço e até a próxima!
Portuguese 
English