Implicit conversion of varchar value to varchar - collation conflict

sql-server

Solution

Seems that you have different collation types on your database. Then you can have issues when joining tables or to tables in other databases, as in this case. To get around this you can specify the collation of columns or force a collation using the COLLATE clause when joining two columns. Check more info about collation in MSDN

Also you need to specify the COLLATE clause where you are using the problematic column(s). Check this answer, it answer a very similar question

It is better to stick to a single collation globally. Otherwise you will have problems.

See a detailed explanation of the different collation styles

Edited: to check column collation use this snippet

SELECT name, collation_name
  FROM sys.columns
  WHERE OBJECT_ID IN (SELECT OBJECT_ID
                        FROM sys.objects
                       WHERE type = 'U'
                         AND name = 'your_table_name'
                      )
    AND name = 'your_column_name'

Edited: added snippet to get all columns with different collation on a database

SELECT [TABLE_NAME] = OBJECT_NAME([id]),
       [COLUMN_NAME] = [name],
       [COLLATION_NAME] = collation
  FROM syscolumns
 WHERE collation <> 'your_database_collation_type'
   AND collation IS NOT NULL
   AND OBJECTPROPERTY([id], N'IsUserTable')=1

Problem

I'm getting the following error whilst running this script. I've tried using the following: COLLATE Latin1_General_CI_AS. Please can it be sorted? Thanks Msg 457, Level 16, State 1, Line 8 Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict ``` DECLARE @AccountID INT SET @AccountID = 12 SELECT TOP 1 ac.AccountID, co.Email, ao.AccountOptionID FROM CRM.acc.Account ac INNER JOIN CRM.[profile].[Profile] pr ON pr.ProfileID = ac.ProfileFK INNER JOIN CRM.[profile].Contact co ON pr.ProfileID = co.ProfileFK LEFT JOIN CRM.acc.[AccountOption] ao ON ao.AccountFK = ac.AccountID LEFT JOIN ( SELECT OptionID FROM CRM.acc.[Option] WHERE [Name] = 'SMS messages') op ON op.OptionID = ao.OptionFK WHERE ac.AccountID = @AccountID UNION ALL SELECT u.UnsubscribeID, u.EmailAddress, u.SentEmailFK FROM Email.dbo.Unsubscribe u INNER JOIN ( SELECT CASE WHEN AccountTypeFK = 2 THEN OnlineBillingEmail ELSE EmailBillingEmail END [EmailAddress] FROM CRM.acc.Account WHERE AccountID = @AccountID ) ace ON ace.EmailAddress COLLATE DATABASE_DEFAULT = u.EmailAddress COLLATE DATABASE_DEFAULT WHERE ISNULL(ace.EmailAddress, '') != '' ```

Original source

Related problems