viernes, 30 de marzo de 2012

Cannot resolve collation conflict for equal to operation

Collation in SQL Server can be set at column level.

If you have differente collation in two columns, and you try to make some kind of query with one of these things:

1) Join predicates

2) Where clauses

3) Use more than one database or temporal tables  (for example: TempDB with different collation than the other database).

4) Functions

The error message is:

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Modern_Spanish_CI_AS" in the equal to operation.

(Latin1_General_CI_AI and Modern_Spanish_CI_AS are collation examples).

See the example to resolve collation column conflict:

Test1.Col1 ->Latin1_General_CI_AI (DATABASE_DEFAULT)

Test2.Col2 ->Modern_Spanish_CI_AS

You can set the COLLATE to Query:

 

SELECT ID FROM Test1


INNER JOIN Test2


WHERE Test1.Col1 = Test2.Col1 COLLATE DATABASE_DEFAULT (or COLLATE Latin1_General_CI_AI)



Another options are set the same Collation to any level column, database, or servers.

To see the collation database, go to properties database, or use this:

SELECT DATABASEPROPERTYEX('DATABASE', 'Collation') as DB;


No hay comentarios:

Publicar un comentario