SQL SERVER COLLATION PROBLEM

Asked

Viewed 696 times

-1

Someone can help with this Union of tables with Collates, I searched in Google and did not understand very well.

error : Cannot resolve the collation Conflict between "Sql_latin1_general_cp850_ci_ai" and "Sql_latin1_general_cp850_ci_as" in the UNION Operation.

Select MAX(SERV_DH_ULTIMAATU) AS RECEBIMENTO ,EMPR_DS_EMPRESA, DATEDIFF(MI, MAX(SERV_DH_ULTIMAEXEC), GETDATE()) AS PERIODO
                             from  A1.DBO.CS_CDTB_SERVICO_SERV SERV (NOLOCK)
                             INNER JOIN   A1.DBO.CS_CDTB_EMPRESA_EMPR EMPR ON
                             EMPR.ID_EMPR_CD_EMPRESA = SERV.ID_EMPR_CD_EMPRESA
                             where SERV_DS_SERVICO LIKE '%AGENTE DO CLASSIFICADOR DE EMAIL%'    
                             AND EMPR.EMPR_IN_INATIVO = 'N'AND EMPR.EMPR_DS_EMPRESA NOT LIKE '%(NÃO USAR)%' 
                             GROUP BY EMPR_DS_EMPRESA union         

                               Select MAX(SERV_DH_ULTIMAATU) AS RECEBIMENTO ,EMPR_DS_EMPRESA, DATEDIFF(MI, MAX(SERV_DH_ULTIMAEXEC), GETDATE()) AS PERIODO
                             from  A2.DBO.CS_CDTB_SERVICO_SERV SERV (NOLOCK)
                             INNER JOIN   A2.DBO.CS_CDTB_EMPRESA_EMPR EMPR ON
                             EMPR.ID_EMPR_CD_EMPRESA = SERV.ID_EMPR_CD_EMPRESA
                             where SERV_DS_SERVICO LIKE '%AGENTE DO CLASSIFICADOR DE EMAIL%'    
                             AND EMPR.EMPR_IN_INATIVO = 'N'AND EMPR.EMPR_DS_EMPRESA NOT LIKE '%(NÃO USAR)%' 
                             GROUP BY EMPR_DS_EMPRESA
sql-server

1 answer

2

Brian, probably the two databases are in different COLLATIONS.

One is in SQL_Latin1_General_CP850_CI_AI and another is in SQL_Latin1_General_CP850_CI_AS (note the endings AI and AS that differentiates them in the error presented!).

Thus, when performing UNION, Sqlserver cannot join different types of Collations.

Solution 1

Change the collation of one of the databases to be the same as the other.

Solution 2

Utilize COLLATE Latin1_General_CI_AS after each select field to convert them. So the UNION would be able to join the fields. This way:

SELECT
     EMPR_DS_EMPRESA COLLATE Latin1_General_CI_AS,
     CAMPO_2 COLLATE Latin1_General_CI_AS
FROM A2.DBO.CS_CDTB_SERVICO_SERV

Reference: https://stackoverflow.com/questions/25260659/collation-conflict-in-sql-union-all-query

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.