DDL

martedì 29 settembre 2015 #

Sql Server e COLLATE

In questi giorni mi sono trovato dover fare query tra database differenti e mi si è presentato il seguente errore:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation

Il collate è la clausola che è possibile applicare a una definizione di database o di colonna per definire le regole di confronto oppure a un'espressione stringa di caratteri per applicare il casting delle regole di confronto.

Ovviamente se DB differenti hanno collate differenti non può confrontare i campi (ma ci sono gli stessi problemi con un select/insert tra database)

Una soluzione è forzare un collate nella query:

SELECT search.* FROM categories 
    INNER JOIN search ON categories.cid = search.cat_id collate SQL_Latin1_General_CP1_CI_AS

Ma ovviamente se il problema è in tutta l’applicazione questa modifica potrebbe essere eccessivamente invasiva Confused smile

Cambiare il collate nelle opzioni del database non è la soluzione, poiché le colonne già create si tengono il loro collate…

Ma qui ho trovato la soluzione… una query da lanciare per avere gli script di migrazione belli pronti:

DECLARE @collate SYSNAME
SELECT @collate = 'Cyrillic_General_CS_AS'

SELECT 
      '[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name
    , 'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']
        ALTER COLUMN [' + c.name + '] ' +
        UPPER(t.name) + 
        CASE WHEN t.name NOT IN ('ntext', 'text') 
            THEN '(' + 
                CASE 
                    WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length != -1 
                        THEN CAST(c.max_length / 2 AS VARCHAR(10))
                    WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length = -1 
                        THEN 'MAX'
                    ELSE CAST(c.max_length AS VARCHAR(10)) 
                END + ')' 
            ELSE '' 
        END + ' COLLATE ' + @collate + 
        CASE WHEN c.is_nullable = 1 
            THEN ' NULL'
            ELSE ' NOT NULL'
        END
FROM sys.columns c WITH(NOLOCK)
JOIN sys.objects o WITH(NOLOCK) ON c.[object_id] = o.[object_id]
JOIN sys.types t WITH(NOLOCK) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')
    AND c.collation_name != @collate
    AND o.[type] = 'U'