Implicit conversion of varchar value to varchar - collation conflict
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, '') != ''
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