Cannot resolve collation conflict on column 5 in select statement

Question

I am trying to show a combination of several fields as only one. (I was asked by the customer to do so). I tried the following command but I get the aforementioned error:

The SQL snippet:

SELECT        dbo.VPayment_2.Serial, dbo.VPayment_1.Description, dbo.VPayment_2.Money, dbo.VPayment_1.PaymentType,, 
                         'Payment ' + dbo.VPayment_1.PaymentType + ' - ' + ' Serial ' + CAST(dbo.VPayment_2.Serial AS nvarchar(15)) 
                         + '(' + dbo.VPayment_1.Description + ')' AS Compact Info 
FROM            dbo.VPayment_2 INNER JOIN
                         dbo.VPayment_1 ON dbo.VPayment_2.ID = dbo.VPayment_1.ID

So that the end result would look like:

---------------------------------------------------------------------------
Serial  Description    Money    PaymentType         Compact Info
123456  some info      500$        Check      Payment :Check - Serial 123456 - some info 
0       some info2     100$      In Cash      Payment :In Cash - Serial 0 - some info   

To achieve this I created two views out of the table in question and then using the IDs I inner joined these two views so that I get the spectated result. The problem is when I add the Compact Info field which is the combination of other fields it generates the error:

Can not resolve collation conflict on column 5 in select statement

and this happens when I am not comparing anything.

Answer 1

Sounds like dbo.VPayment_1.PaymentType and dbo.VPayment_2.Serial are of different collation (this is not just happening when you compare, but also when you union, concatenate, etc).

There are a couple of fixes:

1. Change one view or both to use the same collation for those columns. You can do this by changing the underlying column(s) or by specifying a COLLATE clause in the SELECT list of the view, e.g.

   SELECT PaymentType COLLATE Latin1_General_CI_AI
   ... FROM ...
   

2. Explicitly collate within your query, e.g.

   ... 'Payment ' + dbo.VPayment_1.PaymentType COLLATE Latin1_General_CI_AI
       + ' - ' + ' Serial ' + CAST(dbo.VPayment_2.Serial COLLATE Latin1_General_CI_AI
       AS nvarchar(15))
   

3. The best solution would be to correct all string columns to use the same collation. I have come across many cases where people were using different collation in different tables, databases or servers, and have yet to get a valid explanation - most of the time they had no idea why the collations were different.