Internal Query Processor Error: The query processor could not produce a query pl

Question

Internal Query Processor Error: The query processor could not produce a query pl

YSLGuru

SSC-Insane

Points: 21632
More actions
October 1, 2020 at 6:58 pm

#3792353
Is there a standard method for troubleshooting the below error?
Internal Query Processor Error: The query processor could not produce a query plan
I know its probably the most popular reply to posts like this but I'm not allowed to post the DDL to recreate the involved tables. I could provide a facsimile if that's the only way to troubleshoot this but it will take time to create. I can post a copy of the query with the table and column names changed .
I ran the below query last night many items (its contained in a report our accounting software uses) and every single time the result was this error msg. Today I re-ran those same reports requests and every one ran without any error so as of today I'm unable to produce the error when running the same query as last night.
```
  SELECT P.ID, 
         T.ID,
         TS.Status,
         C.ID,
         A.ID
         CT.ID
  
  FROM TABLEP P Join TABLET T    ON P.ID = T.P_ID  
                Join TABLETS TS  ON T.Status = TS.Status
                Join TABLEC C    ON T.Person = C.TS_Person
                Join TABLEA A    ON A.ID = C.A_ID  
     Left Outer Join TABLECT CT  ON C.CT_ID = CT.ID

  WHERE 1 = 1 
    AND C.Type = 7  
    AND P.ID IN(1,2,3,4,5,6,7,8,9)
    AND C.Date BETWEEN '2020/10/01' AND '2020/10/01'
```
When I was getting this error last night with the query I found I could get the query to run if I made one small change to one of the table joins as shown below between TABLEP and TABLEC. By adding a conditioning to the join between TABLEC and TABLEP the query ran without any error and very quickly. TABLEC has a Foreign Key column that links back to the Primary Key for TABLEP. That is there because there are times when you'd pull data from TABLEP and TABLEC without going thru TABLET and TABLETS.
ID is a Primary Key column and P_ID is the foreign key.
```
  SELECT P.ID, 
         T.ID,
         TS.Status,
         C.ID,
         A.ID
         CT.ID
  
  FROM TABLEP P Join TABLET T    ON P.ID = T.P_ID  
                Join TABLETS TS  ON T.Status = TS.Status
                Join TABLEC C    ON T.Person = C.TS_Person AND C.P_ID = P.ID /*<<<THIS CHANGE*/
                Join TABLEA A    ON A.ID = C.A_ID  
     Left Outer Join TABLECT CT  ON C.CT_ID = CT.ID

  WHERE 1 = 1 
    AND C.Type = 7  
    AND P.ID IN(1,2,3,4,5,6,7,8,9)
    AND C.Date BETWEEN '2020/10/01' AND '2020/10/01'
```
Kindest Regards,
Just say No to Facebook!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

You don't have the error anymore so it's hard to say. I have seen it come up when statistics were out of date and that would be my first guess of what may have happened in your case. I don't know that there is a standard way to troubleshoot the error other than going through the query, make sure the logic makes sense, remove any hints (since sometimes they don't make sense and wouldn't apply) and update the statistics.

Sue