CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expressiontype-mismatch
Cedar Bristol
Cedar Bristol

Reputation: 119

Recursive CTE Error: Types do not match

I have this CTE 

WITH items AS ( 
        SELECT 1 AS lvl, 
               i.[No_] [Parent Item No_], 
               i.[No_], 
               i.[Description], 
               CAST(N'' AS NVARCHAR(20)) /* COLLATE Latin1_General_100_CS_AS  */ 
        FROM Item i
        LEFT JOIN KitComponent bc ON bc.[Parent Item No_] = i.[No_] 
        LEFT JOIN Item ci ON ci.[No_] = bc.[No_] 
        GROUP BY i.[No_], i.[Description], i.[Unit Price] 

        UNION ALL

        SELECT i.lvl + 1, 
               i.[No_], 
               i2.[No_], 
               i2.[Description], 
               CAST(bc.[Variant Code] AS NVARCHAR(20)) 
        FROM KitComponent bc
        JOIN items i ON i.[No_] = bc.[Parent Item No_]
        JOIN Item i2 ON i2.[No_] = bc.[No_] 
) 
SELECT * FROM items WHERE [Parent Item No_] = '4000540001'

It works on one server, without that commented part specifying the collation. Then I copy the whole thing into another window where a replicated copy of the same database lives on another server and get this error.

Types don't match between the anchor and the recursive part in column "Variant Code" of recursive query "items".

I originally didn't have those CAST functions either, it worked just fine on server #1. Finally I put the collate command in the top part of the CTE and then it works on both machines.

I check the version by SELECT @@VERSION

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) 
  Oct 20 2015 15:36:27 
  Copyright (c) Microsoft Corporation
  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
  (Hypervisor)

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) 
  Oct 20 2015 15:36:27 
  Copyright (c) Microsoft Corporation
  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
  (Hypervisor)

and they are identical, so I think this must be some option setting that's different.

Does anybody know where I look to see what option that is?

Upvotes: 2

Views: 1332

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

From WITH doc:

The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.

So it should have the same datatype,length,collation, ....

In your example:

WITH items AS ( 
        SELECT 1 AS lvl, 
               i.[No_] [Parent Item No_], 
               i.[No_], 
               i.[Description], 
               CAST(N'' AS NVARCHAR(20)) -- default DB collation
        FROM Item i
        LEFT JOIN KitComponent bc ON bc.[Parent Item No_] = i.[No_] 
        LEFT JOIN Item ci ON ci.[No_] = bc.[No_] 
        GROUP BY i.[No_], i.[Description], i.[Unit Price] 

        UNION ALL

        SELECT i.lvl + 1, 
               i.[No_], 
               i2.[No_], 
               i2.[Description], 
               CAST(bc.[Variant Code] AS NVARCHAR(20)) -- column collation
        FROM KitComponent bc
        JOIN items i ON i.[No_] = bc.[Parent Item No_]
        JOIN Item i2 ON i2.[No_] = bc.[No_] 
) 
SELECT * FROM items WHERE [Parent Item No_] = '4000540001';

Check

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation')

and:

SELECT COLLATION_NAME,*
FROM INFORMATION_SCHEMA.COLUMNS      
WHERE table_name = 'KitComponent'
  AND column_name = 'Variant Code'

Upvotes: 2

Related Questions