Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation

Question

Need help here. I have 2 questions based on this query.

SELECT cdd.FieldID,cdd.PlanTypeID,pt.IsFinancial            
FROM ClientDataDictionary cdd
    INNER JOIN #tblPlanTypes pt ON cdd.PlanTypeID = pt.PlanTypeID
    INNER JOIN ClientDataDictionaryFieldType cddftype
    ON cdd.FieldId = cddftype.FieldID AND cdd.PlanTypeId = cddftype.PlanTypeID
    WHERE cdd.ClientId = @ClientID AND cdd.IsHidden = 0
    AND cddftype.FieldTypeID = 4 
    AND cddftype.FieldID = cddftype.ParentFieldIDSectionTitle 
    AND  (SELECT COUNT(cddftype2.FieldID)
              FROM ClientDataDictionaryFieldType  cddftype2
              Where cddftype2.ParentFieldIDSectionTitle = cdd.FieldID
              AND cddftype2.PlanTypeID = cdd.PlanTypeID
              AND EXISTS(Select tbl.FieldID From #tblSelectedFields tbl
                          Where tbl.FieldID = cddftype2.FieldID AND tbl.PlantypeID =cdd.PlanTypeID COLLATE SQL_Latin1_General_CP1_CI_AS)
              ) != 0 COLLATE SQL_Latin1_General_CP1_CI_AS

1. Where do I place my COLLATE keyword in this query? I know that temp tables have different collate "type".
2. I tried using COLLATE DATABASE_DEFAULT however, the error still occurs. Will this resolve the issue?

Please help me here. Thanks!

[UPDATE 1] ClientID is set to UNIQUEIDENTIFIER data type.

[UPDATE 2] here are the table definitions

#tblPlanTypes

PlanTypeId nvarchar(50),  
PlanType nvarchar(max),  
Prefix NVARCHAR(10),  
IsFinancial BIT,  
TotalCount BIGINT,  
PlanCount BIGINT  

ClientDataDictonary

[ClientDataDictionaryId] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT      [DF_ClientDataDictionary_ClientDataDictionaryId] DEFAULT (newid()),
[ClientId] [uniqueidentifier] NOT NULL,
[PlanTypeId] [uniqueidentifier] NULL,
[FieldId] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FieldText] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FieldType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DefaultValue] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HelpText] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsHidden] [bit] NULL CONSTRAINT [DF_ClientDataDictionary_IsHidden] DEFAULT ((0)),
[IsSummable] [bit] NULL,
[IsRequired] [bit] NULL,
[IsContractRenewal] [bit] NULL,
[HasAlert] [bit] NULL CONSTRAINT [DF_ClientDataDictionary_HasAlert] DEFAULT ((0)),
[AlertMessage] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CentralLanguageId] [uniqueidentifier] NOT NULL,
[LookupTypeName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RowVersion] [timestamp] NULL,
[LastUpdated] [datetime] NULL CONSTRAINT [DF_ClientDataDictionary_LastUpdated] DEFAULT (getutcdate()),
[Sequence] [int] NULL

ClientDataDictionaryFieldType

[ClientDataDictionaryFieldTypeID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClientDataDictionaryFieldType_ClientDataDictionaryFieldType] DEFAULT (newid()),
[PlanTypeID] [uniqueidentifier] NULL,
[FieldID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FieldTypeID] [int] NULL,
[ParentFieldIDSectionTitle] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

#tblSelectedFields

FieldID nvarchar(255),  
PlanTypeID UniqueIdentifier,  
FieldText nvarchar(MAX),  
sequence int,  
FieldType nvarchar(100),  
IsFinancial bit  

[UPDATE 3]

Tried the suggestion by @Mack, but a new error emerged. Expression type uniqueidentifier is invalid for COLLATE clause. This is caused by the @ClientID having a data type of uniqueidentifier. Any suggestions?

Answer 1

First of all - if you include the descriptions of the tables in your example people here will be able to help you a lot quicker.

Second to debug this query yourself break it into logical parts(separate any subqueries, remove tables one at a time), then remove the where clauses one by from each logical part until your query works, this will help you identify the cause of the problem, remember that the join clauses could also be the problem.

Finally, the code below will allow you to determine the collation type for each column you are querying, it's from this article by Pinal Dave

USE yourdb
GO
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
                     FROM sys.objects
                     WHERE type = 'U'
                     AND name = 'yourtable')
AND name IN('yourcolumn1','yourcolumn2',.....,'yourcolumnX')

Thanks for the table definitions... At first glance, your collate issue lies with this field

PlanTypeId nvarchar(50)

used in this join: INNER JOIN #tblPlanTypes pt ON cdd.PlanTypeID = pt.PlanTypeID if you apply the COLLATE to the join you should resolve your problem.

Answer 2

You need to place COLLATE keywords after textual (char, nchar, varchar, nvarchar) field names in comparison/join operators - 'alias1.txtfield1 COLLATE something = alias2.txtfield2 COLLATE something'. There's no point in using COLLATE with integer fields (I hope your IDs are of integer variety?).