Types don't match between the anchor and the recursive part...

[MarcCama]

Dear All,

I have a SQL Server Express 2005 database installed on a computer with Windows Vista. When I run the query below:

WITH AE_AIR_SYSTEM_RECURSIVE (SQ_SYSTEM, CL_SYSTEM_CLEAR) AS (
SELECT AISY_SQ_AIR_SYSTEM
, CAST('' AS VARCHAR(MAX))
FROM EHS.AE_AIR_SYSTEM WHERE AISY_SQ_AIR_SYSTEM_FATHER = 137
UNION ALL
SELECT S.AISY_SQ_AIR_SYSTEM
, CASE WHEN CL_SYSTEM_CLEAR = '' THEN '' ELSE CL_SYSTEM_CLEAR + '/' END + CAST(S.AISY_NM_AIR_SYSTEM AS VARCHAR(MAX))
FROM EHS.AE_AIR_SYSTEM S
INNER JOIN AE_AIR_SYSTEM_RECURSIVE SAI ON S.AISY_SQ_AIR_SYSTEM_FATHER = SQ_SYSTEM)
SELECT * FROM AE_AIR_SYSTEM_RECURSIVE

I get the following result:

SQ_SYSTEM CL_SYSTEM_CLEAR
138
139
143
144 General Exhaust
141 Main Power Generator
142 Secondary Power Generator
140 Main Boiler

But, when I installed the SQL Server Express 2005 on a computer with Windows 7,the same query returns the following message error:

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

But surprise! When I replaced the CAST('' AS VARCHAR (MAX)) in the first SELECT to CAST(AISY_NM_AIR_SYSTEM AS VARCHAR (MAX)), no more error appears, although not show the expected result, as follows:

SQ_SYSTEM CL_SYSTEM_CLEAR
138 Athletics Stadium
139 Soccer Stadium
143 Multisport Gym
144 Multisport Gym/General Exhaust
141 Soccer Stadium /Main Power Generator
142 Soccer Stadium /Secondary Power Generator
140 Athletics Stadium /Main Boiler

because the initial portion could not appear in the concatenated column.

To clarify, the structure of the EHS.AE_AIR_SYSTEM is as follows:

Column Name Data Type
AISY_SQ_AIR_SYSTEM Int
AISY_SQ_AIR_SYSTEM_FATHER Int
AISY_NM_AIR_SYSTEM varchar(50)
AISY_DS_AIR_SYSTEM varchar(1500)

And have a script for table creation below.

====== BEGIN SCRIPT ===============
/****** Object: Table [EHS].[AE_AIR_SYSTEM] Script Date: 06/17/2010 15:49:59 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[EHS].[AE_AIR_SYSTEM]') AND type in (N'U'))
DROP TABLE [EHS].[AE_AIR_SYSTEM]
GO
/****** Object: Table [EHS].[AE_AIR_SYSTEM] Script Date: 06/17/2010 15:49:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[EHS].[AE_AIR_SYSTEM]') AND type in (N'U'))
BEGIN
CREATE TABLE [EHS].[AE_AIR_SYSTEM](
[AISY_SQ_AIR_SYSTEM] [int] IDENTITY(1,1) NOT NULL,
[AISY_SQ_AIR_SYSTEM_FATHER] [int] NULL,
[AISY_NM_AIR_SYSTEM] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
[AISY_DS_AIR_SYSTEM] [varchar](1500) COLLATE Latin1_General_CI_AI NULL
)
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[EHS].[AE_AIR_SYSTEM]') AND name = N'XPKAE_AIR_SYSTEM_T')
CREATE UNIQUE CLUSTERED INDEX [XPKAE_AIR_SYSTEM_T] ON [EHS].[AE_AIR_SYSTEM]
(
[AISY_SQ_AIR_SYSTEM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
SET IDENTITY_INSERT [EHS].[AE_AIR_SYSTEM] ON
INSERT [EHS].[AE_AIR_SYSTEM] ([AISY_SQ_AIR_SYSTEM], [AISY_SQ_AIR_SYSTEM_FATHER], [AISY_NM_AIR_SYSTEM], [AISY_DS_AIR_SYSTEM]) VALUES (137, NULL, N'Sports Center', N'Complexo esportivo que foi construído inicialmente para a Copa do Mundo de Futebol de 1950, mas depois aumentado com um estadio de atletismo e um parque aquatico.')
INSERT [EHS].[AE_AIR_SYSTEM] ([AISY_SQ_AIR_SYSTEM], [AISY_SQ_AIR_SYSTEM_FATHER], [AISY_NM_AIR_SYSTEM], [AISY_DS_AIR_SYSTEM]) VALUES (138, 137, N'Athletics Stadium', N'Pista de atletismo')
INSERT [EHS].[AE_AIR_SYSTEM] ([AISY_SQ_AIR_SYSTEM], [AISY_SQ_AIR_SYSTEM_FATHER], [AISY_NM_AIR_SYSTEM], [AISY_DS_AIR_SYSTEM]) VALUES (139, 137, N'Soccer Stadium', N'Estadio de futebol')
INSERT [EHS].[AE_AIR_SYSTEM] ([AISY_SQ_AIR_SYSTEM], [AISY_SQ_AIR_SYSTEM_FATHER], [AISY_NM_AIR_SYSTEM], [AISY_DS_AIR_SYSTEM]) VALUES (140, 138, N'Main Boiler', N'Caldeira geradora de vapor, fabricada pela ATA, modelo MP-806, movida a GLP.')
INSERT [EHS].[AE_AIR_SYSTEM] ([AISY_SQ_AIR_SYSTEM], [AISY_SQ_AIR_SYSTEM_FATHER], [AISY_NM_AIR_SYSTEM], [AISY_DS_AIR_SYSTEM]) VALUES (141, 139, N'Main Power Generator', N'Gerador de 550 KWA QP, modelo GFD56.')
INSERT [EHS].[AE_AIR_SYSTEM] ([AISY_SQ_AIR_SYSTEM], [AISY_SQ_AIR_SYSTEM_FATHER], [AISY_NM_AIR_SYSTEM], [AISY_DS_AIR_SYSTEM]) VALUES (142, 139, N'Secondary Power Generator', N'Gerador de 450 KWA, motor ummins, modelo NTA 855-G3.')
INSERT [EHS].[AE_AIR_SYSTEM] ([AISY_SQ_AIR_SYSTEM], [AISY_SQ_AIR_SYSTEM_FATHER], [AISY_NM_AIR_SYSTEM], [AISY_DS_AIR_SYSTEM]) VALUES (143, 137, N'Multisport Gym', N'Ginasio poliesportivo')
INSERT [EHS].[AE_AIR_SYSTEM] ([AISY_SQ_AIR_SYSTEM], [AISY_SQ_AIR_SYSTEM_FATHER], [AISY_NM_AIR_SYSTEM], [AISY_DS_AIR_SYSTEM]) VALUES (144, 143, N'General Exhaust', N'Somatoria das emissoes de VOCs por Alcool e Formol.')
SET IDENTITY_INSERT [EHS].[AE_AIR_SYSTEM] OFF
====== END SCRIPT ===============

Why occurs this error? What do I do to solve it?

Thank you, Marcelo Camarate

 

[markros]

I got the same error as you and so far I'm also a bit puzzled and was not able to resolve it yet.

PluralSight Learning Library

 

[MarcCama]

Markros,

In my case I fixed the error replaced CAST('' AS VARCHAR(MAX)) by CAST(SUBSTRING(AISY_NM_AIR_SYSTEM,1,0) AS VARCHAR(MAX)) in the SQL query.

Regards, Marcelo Camarate

 

[markros]

Interesting, why this change resolved the problem. BTW, I got empty result, but no error anymore.

PluralSight Learning Library

 

[MarcCama]

Markros,

I don't know why too. I think that should be some configuration problem in the Windows 7 and SQL Server 2005 Express environment, because when the old query run under a Windows Vista and SQL Server 2005 Express, it works OK.

But the important is that's working OK now. Let us leave the explanations to the experts.

Regards, Marcelo Camarate