Cannot resolve the collation conflict - SQL Server 2008 R2 SP1

Question

I'm trying to run the query below:

SELECT [id]
      ,[company_id]
      ,[entry_no_]
      ,[project_no_sk]
      ,[project_no_]
      ,[task_code_sk]
      ,[task_code]
      ,[resource_code_sk]
      ,[resource_code]
      ,[work_type_code_sk]
      ,[work_type_code]
      ,[date_sk]
      ,[date]
      ,(SELECT  [Price]
        FROM    [helios_navision_data_mart].[dbo].[dim_resource_prices]
        WHERE   [Project No_]       = [project_no_]
        AND     [Work Type Code]    = [work_type_code]
        AND     [Task Code]     =   CASE WHEN [Task Code] IN (task_code) 
                                                    THEN task_code
                                        ELSE ''
                                    END
        ) as [Unit Price]
      ,[quantity]
  FROM [dbo].[fact_operational_time_entry]
  WHERE project_no_ = 'CHM-001'
  AND [chargeable] = 'Yes'
  AND [approved] = 'No'

But when executing this query I receive the error:

Msg 468, Level 16, State 9, Line 17
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "Latin1_General_CS_AS" in the equal to operation.

I have checked to collation of the tables, the database and the server and they all have "Latin1_General_CS_AS" as collation defined. The error occured somewhere in the WHERE-clause but I don't see where..since the collations are the same.

Anyone has an idea? Please help.

KR, Kevin

Answer 1

Don't know why, but I had to modify the subquery so that it forces a collation change..

,(SELECT    [Price]
        FROM    [helios_navision_data_mart].[dbo].[dim_resource_prices]
        WHERE   [Project No_]       = [project_no_] COLLATE Latin1_General_CS_AS
        AND     [Work Type Code]    = [work_type_code] COLLATE Latin1_General_CS_AS
        AND     [Task Code]         =   (CASE WHEN [Task Code] IN ([task_code] COLLATE Latin1_General_CS_AS) 
                                                    THEN [task_code] COLLATE Latin1_General_CS_AS
                                                ELSE ''
                                        END)
        ) as [Unit Price]