Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 985
  • Last Modified:

SQL Server 2005 - Type mismatch in recursive CTE

Hi,

I'm having a problem using a CTE to generate a recursive recordset.

When the CTE is run on one database, it works fine. However, on a second database with the same exact schema (checked using RedGate SQL Compare) it generates the following error - "Types don't match between the anchor and the recursive part in column "nSort" of recursive query "t".

I've tried just about every variation of the line in question, such as...

CAST(t.nSort + '|' + c.category_name AS varchar(MAX))
t.nSort + '|' + CONVERT(varchar(MAX),c.category_name)

...using variations of CAST and CONVERT with different datatypes on both the anchor and recursive side of the UNION, moved the pipe around etc... but even a simple...

CAST(c.category_name AS varchar(MAX))

...doesn't work.

I'm stumped here. I can't see why it would work on one database but not the other when they have the same schema. Maybe some dodgy data is messing it up, but if that's the case, why isn't the CAST fixing it?

Thanks in advance!

WITH t AS (
	SELECT		0 AS category_id,
			CAST(0 AS varchar(MAX)) AS nPath,
			0 AS treeLevel,
			CAST('home' AS varchar(MAX)) AS nSort
		UNION ALL 
	SELECT		c.category_id,
			CAST(c.category_id AS varchar(MAX)) + '|' + t.nPath,
			t.treeLevel + 1,
			t.nSort + CAST('|' + c.category_name AS varchar(MAX))
	FROM		t
	INNER JOIN	category c ON (c.category_memberof = t.category_id)
	)
 
SELECT 		*
FROM		t
ORDER BY	nSort

Open in new window

0
jens-gm
Asked:
jens-gm
  • 14
  • 11
1 Solution
 
jens-gmAuthor Commented:
It's line 10 that is causing the problem. I realise it's unconventional to use a string for sorting like this, but there's good reason for it.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the data type of c.category_id?
0
 
jens-gmAuthor Commented:
Full column info below - the c.category_id part works (if i remove lines 5 and 10 from the query then it runs fine)
category_id		int		not null
category_memberof	int		not null
category_name		nvarchar(100)	not null

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jens-gmAuthor Commented:
I've tried changing the query to use nvarchars by the way, and changed the column size around (e.g. using nvarchar(255) instead of varchar(max))
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, this worked perfectly for me:
create table category ( category_id             int             not null
, category_memberof       int             not null
, category_name           nvarchar(100)   not null
)
go
insert into category values ( 1, 0, 'root' )
insert into category values ( 2, 1, 'leaf' )
go
 
 
WITH t AS (
        SELECT          0 AS category_id,
                        CAST(0 AS varchar(MAX)) AS nPath,
                        0 AS treeLevel,
                        CAST('home' AS varchar(MAX)) AS nSort
                UNION ALL 
        SELECT          c.category_id,
                        CAST(c.category_id AS varchar(MAX)) + '|' + t.nPath,
                        t.treeLevel + 1,
                        t.nSort + CAST('|' + c.category_name AS varchar(MAX))
        FROM            t
        INNER JOIN      category c ON (c.category_memberof = t.category_id)
        )
 
SELECT          *
FROM            t
ORDER BY        nSort
go
drop table category

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
data returned:
(1 row(s) affected)
 
(1 row(s) affected)
category_id nPath                                                                                                                                                                                                                                                            treeLevel   nSort
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0           0                                                                                                                                                                                                                                                                0           home
1           1|0                                                                                                                                                                                                                                                              1           home|root
2           2|1|0                                                                                                                                                                                                                                                            2           home|root|leaf
 
(3 row(s) affected)

Open in new window

0
 
jens-gmAuthor Commented:
Yes, that worked for me to. I've adapted it slightly to use my actual data as below, and that works too - but running on the original table still errors!
create table category2 (
	category_id int not null,
	category_memberof int not null,
	category_name nvarchar(100) not null
	)
go
 
insert into category2 (category_id,category_memberof,category_name)
select category_id,category_memberof,category_name
from category
go
 
WITH t AS (
        SELECT          0 AS category_id,
                        CAST(0 AS varchar(MAX)) AS nPath,
                        0 AS treeLevel,
                        CAST('home' AS varchar(MAX)) AS nSort
                UNION ALL 
        SELECT          c.category_id,
                        CAST(c.category_id AS varchar(MAX)) + '|' + t.nPath,
                        t.treeLevel + 1,
                        t.nSort + CAST('|' + c.category_name AS varchar(MAX))
        FROM            t
        INNER JOIN      category2 c ON (c.category_memberof = t.category_id)
        )
 
SELECT          *
FROM            t
ORDER BY        nSort
go
 
drop table category2

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about:
WITH t AS (
        SELECT          0 AS category_id,
                        CAST(0 AS varchar(MAX)) AS nPath,
                        0 AS treeLevel,
                        CAST('home' AS varchar(MAX)) AS nSort
                UNION ALL 
        SELECT          c.category_id,
                        CAST(c.category_id AS varchar(MAX)) + '|' + t.nPath,
                        t.treeLevel + 1,
                        CAST(t.nSort + '|' + c.category_name AS varchar(MAX))
        FROM            t
        INNER JOIN      category c ON (c.category_memberof = t.category_id)
        )
 
SELECT          *
FROM            t
ORDER BY        nSort

Open in new window

0
 
jens-gmAuthor Commented:
No, same error >:(
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
one step more:
WITH t AS (
        SELECT          cast(0 as int) AS category_id,
                        CAST(0 AS varchar(MAX)) AS nPath,
                        cast(0 as int) AS treeLevel,
                        CAST('home' AS varchar(MAX)) AS nSort
                UNION ALL 
        SELECT          c.category_id,
                        CAST(c.category_id AS varchar(MAX)) + '|' + t.nPath,
                        t.treeLevel + 1,
                        CAST(t.nSort + '|' + c.category_name AS varchar(MAX))
        FROM            t
        INNER JOIN      category c ON (c.category_memberof = t.category_id)
        )
 
SELECT          *
FROM            t
ORDER BY        nSort

Open in new window

0
 
jens-gmAuthor Commented:
No, same error again. I've also tried using the below in case something with the created first row was causing the problem. Could it be a symptom of a data corruption somewhere?
WITH t AS (
      	SELECT		category_id,
			category_id AS nPath,
			0 AS treeLevel,
			category_name AS nSort
	FROM		category
	WHERE		category_memberof = 0
                UNION ALL 
        SELECT          c.category_id,
                        CAST(c.category_id AS varchar(MAX)) + '|' + t.nPath,
                        t.treeLevel + 1,
                        CAST(t.nSort + '|' + c.category_name AS varchar(MAX))
        FROM            t
        INNER JOIN      category c ON (c.category_memberof = t.category_id)
        )
 
SELECT          *
FROM            t
ORDER BY        nSort

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:
WITH t AS (
        SELECT          category_id,
                        category_id AS nPath,
                        0 AS treeLevel,
                        category_name AS nSort
        FROM            category
        WHERE           category_memberof = 0
                UNION ALL 
        SELECT          c.category_id,
                        CAST( CAST(c.category_id AS VARCHAR(20))  + '|' + t.nPath as varchar(MAX)),
                        t.treeLevel + 1,
                        CAST(t.nSort + '|' + c.category_name AS varchar(MAX))
        FROM            t
        INNER JOIN      category c ON (c.category_memberof = t.category_id)
        )
 
SELECT          *
FROM            t
ORDER BY        nSort

Open in new window

0
 
jens-gmAuthor Commented:
No, no luck there either - both nPath and nSort gave the type mismatch. I've trimmed the query down to the bare essentials as below but it still errors.
WITH t AS (
        SELECT          category_id,
                        category_name AS nSort
        FROM            category
        WHERE           category_memberof = 0
                UNION ALL 
        SELECT          c.category_id,
                        CAST(t.nSort + '|' + c.category_name AS varchar(MAX))
        FROM            t
        INNER JOIN      category c ON (c.category_memberof = t.category_id)
        )
 
SELECT          *
FROM            t
ORDER BY        nSort

Open in new window

0
 
jens-gmAuthor Commented:
When I try the below, that errors too... I can only assume a column not actually used in the query is breaking it - how is that possible?
SELECT *
INTO category2
FROM category
GO
 
WITH t AS (
        SELECT          0 AS category_id,
                        CAST('home' AS varchar(MAX)) AS nSort
                UNION ALL 
        SELECT          c.category_id,
                        t.nSort + CAST(c.category_name AS varchar(MAX))
        FROM            t
        INNER JOIN      category2 c ON (c.category_memberof = t.category_id)
        )
 
SELECT          *
FROM            t
ORDER BY        nSort
GO
 
DROP TABLE category2

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show the entire table structure, please?
what service pack is your sql server?
0
 
jens-gmAuthor Commented:
we're on Service Pack 2, the structure of the table is attached...apologies on the lateness of my reply, have been ill :)
category-table-structure.jpg
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it still works for me...
please post the result of this:
select @@version
0
 
jens-gmAuthor Commented:
ah...it's actually returned this:

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)   Apr 14 2006 01:12:25   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so you are on sql sp 1:
http://support.microsoft.com/kb/321185
0
 
jens-gmAuthor Commented:
strange, i have  now installed SP 2, it reported that it had installed successfully, restarted the server and now it is still showing as 9.00.2047.00. Have you come across that happening before?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>it reported that it had installed successfully,
I am quite sure that you run the .exe that you downloaded, however that one ONLY extracts the actual setup. you have to run the extracted setup...
0
 
jens-gmAuthor Commented:
Additionally I've just run the same query on one of our live servers, which is on 9.00.3054.00 and I've received the same error message
0
 
jens-gmAuthor Commented:
Also, according to the message after installation of the Server back it said "SP2 Installed Successfully" but evidently this isn't the case. However even with SP2 on our other server the problem persists, so evidently it must not be a server pack problem?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree.
however, I have run out of ideas :(
0
 
jens-gmAuthor Commented:
Thanks for all your assistance. We've managed to get it to work by rebuilding the table from scratch and importing the data across. Never did work out why there should be a difference but at least it's working. Points your way for effort! Many thanks :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 14
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now