Home > SQL Server > Cannot resolve the collation conflict

Cannot resolve the collation conflict

2016/03/01

Making an UNION between two SQL Server 2014 views i got the error

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS”

There are many fields, a problem.. fortunately there is an easy solution with

SELECT
col.name, col.collation_name
FROM
sys.columns col
WHERE
object_id = OBJECT_ID('<yourview>')

With this query we get something as (fields name hidden for privacy):
01-03-2016 16-10-38
I tried with the first view and then with the another view in UNION, here is immediately visible which are the problematic fields (the few ones with another collation, in this case “Latin1_General_CI_AS”) .
Then for these fields we can add the collation, as in this case where there is also an CASE WHEN:

CASE WHEN TABDDETB = '' OR TABDDETB IS NULL THEN
   'Not specified' COLLATE SQL_Latin1_General_CP1_CI_AS
   ELSE TABDDETB COLLATE SQL_Latin1_General_CP1_CI_AS
END AS category

and the error is solved.

Advertisements
Categories: SQL Server
%d bloggers like this: