2

I'm getting this message:

Msg 8624, Level 16, State 17, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

This is what I run

;WITH TableA AS
(
   SELECT 101 as A_ID
)
,TableB AS
(
   SELECT 1 as B_ID, 101 as B_A_ID , 'xxx' as B_Courses 
   UNION ALL
   SELECT 2 , 101  , 'YYY'  
   UNION ALL
   SELECT 3 , 101  , 'ZZZ'  
   UNION ALL
   SELECT 4 , 102  , 'AAA' 
)
SELECT
    A_id
    ,x.x.value('(./text())[1]','varchar(500)') AS fieldX
FROM
    TableA AS A
OUTER APPLY
    -- CROSS APPLY
    (SELECT
         ',' + B_Courses
     FROM
         TableB AS B            
     WHERE 
            1=1
            AND B.B_A_ID  = A.A_ID          
     FOR XML PATH(''),TYPE
    ) x(x)

With OUTER APPLY, I get the message shown above.

With CROSS APPLY, everything works nicely.

I resolve by adding in select this :

STUFF((SELECT ','+B_Courses
    FROM
         TableB AS B    
    WHERE 
        B.B_A_ID  = A.A_ID
    FOR XML PATH(''),TYPE).value('(./text())[1]' ,'VARCHAR(500)'),1,1,'')               

Tested on:

Edition           ProductVersion    ProductLevel
Express Edition     9.00.3042.00    SP2

and

Edition                     ProductVersion  ProductLevel
Developer Edition (64-bit)  11.0.2100.60    RTM

Any idea way is this happening?

Thanks

1

You could consider raising a connect item (Microsoft bug report) as you have a reliable "repro", but then there are a number of workarounds for the problem - eg there is no real need for the APPLY, eg

;WITH TableA
AS
    (
    SELECT 101 as A_ID
    ), TableB
AS
    (SELECT 1 as B_ID, 101 as B_A_ID , 'xxx' as B_Courses UNION ALL
    SELECT 2 , 101  , 'YYY'  UNION ALL
    SELECT 3 , 101  , 'ZZZ'  UNION ALL
    SELECT 4 , 102  , 'AAA' 
    )
SELECT
     a.A_id,
    (
    SELECT ',' + B_Courses
    FROM TableB AS B            
    WHERE B.B_A_ID  = A.A_ID          
    FOR XML PATH(''),TYPE
    ).value('.', 'VARCHAR(20)')
FROM TableA A

NB These errors will also be generating 20MB stack dumps (.mdmp) files to your Log directory.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.