T-SQL CTE Error: Types don't match between the anchor and the recursive part

Question

I get the following error when I try to execute a particular recursive CTE:

Msg 240, Level 16, State 1, Line 8
Types don't match between the anchor and the recursive part in column "data_list" of recursive query "CTE".

This is nonsense. Each field is explicitly cast to VARCHAR(MAX). Please help me. I've read many answers to this problem, here and elsewhere, all of which advise explicitly casting the column in question. I'm already doing this, and still get the error.

This code will reproduce the error:

if object_id('tempdb..#tOwner') IS NOT NULL drop table #tOwner;
CREATE TABLE #tOwner(id int identity(1,1), email varchar(max) );
insert into #towner values ( cast('[email protected]'  as varchar(max)));
insert into #towner values ( cast('tsql rage'    as varchar(max)));
insert into #towner values ( cast('[email protected]'  as varchar(max)));
insert into #towner values ( cast('einstein.x.m' as varchar(max)));

;WITH data AS (
    SELECT DISTINCT convert(varchar(max), email) datapoint FROM #tOwner 
), CTE ( data_list, datapoint, length ) AS ( 
        SELECT convert(VARCHAR(max),            ''           ),convert(VARCHAR(max),    ''     ),       0
    UNION ALL
        SELECT convert(VARCHAR(max),d.datapoint+';'+data_list),convert(VARCHAR(max),d.datapoint), length + 1
        FROM CTE c CROSS JOIN data d WHERE d.datapoint > c.datapoint 
)
SELECT D.data_list
FROM ( 
    SELECT data_list, RANK() OVER ( PARTITION BY 1 ORDER BY length DESC ) 
    FROM CTE 
) D ( data_list, rank )
WHERE rank = 1 ;

drop table #tOwner;

If you find it relevant, SELECT left(@@VERSION, 70) returns:

Microsoft SQL Server 2005 - 9.00.4053.00 (X64)   May 26 2009 14:13:01

The query works for me on SS05 (X86 though). And, it works on my SS08 too. Sorry, not much help there, except you have a good query for other versions. — bobs
– bobs, Commented Aug 6, 2010 at 22:09
This totally doesn't work on my SQL Server 2008 R2 Developer x64 instance. Interesting problem. It's starting to smell to me like a bug. I mean, you have really done a good job at boiling this down. — Dave Markle
– Dave Markle, Commented Aug 6, 2010 at 22:16
It didn't work for me, and then it did - after a few minutes of fiddling I ran the query again (replaced the # table with a physical table - then it worked). Then reverted back to the code above and that worked. Looks like a very weird bug. — Will A
– Will A, Commented Aug 6, 2010 at 22:22
Aha - if I run this in one particular database on my PC I get the error - if I run it vs. master, I don't. A clue! Does the collation of your tempdb match the collation of the database in which you're running the query? — Will A
– Will A, Commented Aug 6, 2010 at 22:27

Community · Accepted Answer · 2017-05-23 11:53:46Z

12

Will A's comment on my original post found the key - the collation. My posted query worked for me in the master database, too.

Examining the collation suggested I was on the right track.

SELECT DATABASEPROPERTYEX('crm_mscrm', 'Collation') crmSQLCollation
crmSQLCollation
--------------------
Latin1_General_CI_AI
(1 row(s) affected)

SELECT DATABASEPROPERTYEX('master', 'Collation') masterSQLCollation
masterSQLCollation
----------------------------
SQL_Latin1_General_CP1_CI_AS
(1 row(s) affected)

Some frenzied searching later, I had this monstrosity of code, which

explicitly specifies collation on each column,
successfully executes, and
returns the expected results

To wit:

if object_id('tempdb..#tOwner') IS NOT NULL drop table #tOwner;
CREATE TABLE #tOwner(id int identity(1,1), email nvarchar(max) );
insert into #towner values ( cast('[email protected]'  as nvarchar(max)));
insert into #towner values ( cast('tsql rage'    as nvarchar(max)));
insert into #towner values ( cast('[email protected]'  as nvarchar(max)));
insert into #towner values ( cast('einstein.x.m' as nvarchar(max)));

;WITH data AS (
    SELECT DISTINCT convert(nvarchar(max), email) datapoint FROM #tOwner 
), CTE ( data_list, datapoint, length ) AS ( 
        SELECT convert(nvarchar(max),            ''           ) Collate SQL_Latin1_General_CP1_CI_AS,convert(nvarchar(max),    ''     ) Collate SQL_Latin1_General_CP1_CI_AS,       0
    UNION ALL
        SELECT convert(nvarchar(max),d.datapoint+';'+data_list) Collate SQL_Latin1_General_CP1_CI_AS,convert(nvarchar(max),d.datapoint) Collate SQL_Latin1_General_CP1_CI_AS, length + 1
        FROM CTE c CROSS JOIN data d WHERE d.datapoint > c.datapoint 
)
SELECT D.data_list
FROM ( 
    SELECT data_list, RANK() OVER ( PARTITION BY 1 ORDER BY length DESC ) 
    FROM CTE 
) D ( data_list, rank )
WHERE rank = 1 ;

if object_id('tempdb..#tOwner') IS NOT NULL drop table #tOwner;

Sitting beautifully in my results window is the expected:

data_list
------------------------------------------------
tsql rage;einstein.x.m;[email protected];[email protected];

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Aug 6, 2010 at 22:59

Einstein X. Mystery

4294 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dave Markle Over a year ago

For the record, this should at least be posted as a MS Connect issue. They should at least give you a better error message. This is pretty darned opaque to me. Toughest SQL Server question I've ever seen on SO!

Johny Skovdal Over a year ago

Thank you, thank you, thank you! :D I had the exact same error, with the added data type mismatch as well (took me a while to figure out I had both problems at once), but without the collation help, I'd probably have taken me days to figure out what was wrong (part of a big and complex query / db structure). Thank you both (@WillA) again! :)

primo Over a year ago

Specifying the collation on the anchor worked for me as well.

John Goering Over a year ago

THANK YOU. Been bashing my head on this for ages.

Collectives™ on Stack Overflow

T-SQL CTE Error: Types don't match between the anchor and the recursive part

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related