SQL 2005 v9.0.2047 (SP1) - The query processor could not produce a query plan
Hi Everyone:
*Before* I actually call up Microsoft SQL Customer Support Services and ask them, I wanted to ping other people to see if you have ever ran into this exact error
"Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I would have searched the forums myself, but at this moment in time, search is broken :(
If anyone has run into this error before, what conditions would exist that this could happen? That is, if I can sniff this out with suggestions from the community, I would be happy to do so.
It is an oddity because if I alter a couple subqueries in the where clause [ i.e., where tab.Col = (select val from tab2 where id='122') ]to not have subqueries [hand coded values], then the t-sql result is fine. It's not as if subqueries are oddities... I've used them when appropriate.
fwiw - Not a newbie t-sql guy. ISV working almost daily with t-sql since MS SQL 2000. I have never seen this message before...at least I don't recall ever seeing it.
Thanks in advance for other suggested examination paths.
I am having same problem but for different scenario. Thats what I have:
select
* from (Select * from dbo.vw_gv_Project_ProjectsUsersFunctions) ppivot(count(test) For FunctionName in (
, 
,
, 
, 
, [f]))AS pvt'test' column is an aliased column similar to a column returned by the result. i.e. 'test' is an aliased to 'UserID' column which is also included in the query.
If I put back 'UserID' in the count, the query runs OK.
Thanks in adavance for your assisstance.
Ihab
This code also produces the error... the text is from an incident a while ago that I reported to microsoft.
--I just found a way to break the query engine. Looks like the SQL Server team missed something. I was generating phony data for test cases, by the way.
--Bad code:
DECLARE
@t_Asset TABLE
(Asset_Id INT)INSERT
INTO @t_Asset (Asset_Id) VALUES (1)INSERT
INTO @t_Asset (Asset_Id) VALUES (2)INSERT
INTO @t_Asset (Asset_Id) VALUES (3)DECLARE @Record_id INT,@File_id NVARCHAR(MAX),@SKP_Cust_id NVARCHAR(MAX),@Unique_Barcode NVARCHAR(MAX)SELECT @Record_Id = (SELECT TOP 1 Asset_Id FROM @t_Asset), @file_id = (SELECT LEFT(REPLACE(CAST(NEWID() AS NVARCHAR(50)), '-', ''), 10)),@Unique_Barcode=(SELECT LEFT(REPLACE(CAST(NEWID() AS NVARCHAR(50)), '-', ''), 15))go
--Msg 8624, Level 16, State 116, Line 12
--Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.
--Code that doesn’t fry the optimizing engine:
DECLARE
@t_Asset TABLE(Asset_Id INT)INSERT
INTO @t_Asset (Asset_Id) VALUES (1)INSERT
INTO @t_Asset (Asset_Id) VALUES (2)INSERT
INTO @t_Asset (Asset_Id) VALUES (3)DECLARE @Record_id INT,@File_id NVARCHAR(MAX),@SKP_Cust_id NVARCHAR(MAX),@Unique_Barcode NVARCHAR(MAX)SELECT @Record_Id = (SELECT TOP 1 Asset_Id FROM @t_Asset)SELECT @file_id = (SELECT LEFT(REPLACE(CAST(NEWID() AS NVARCHAR(50)), '-', ''), 10))SELECT @Unique_Barcode=(SELECT LEFT(REPLACE(CAST(NEWID() AS NVARCHAR(50)), '-', ''), 15))I have encountered the same error while executing a simple Insert query - I wouldn't know how to begin to re-write it to make it work. I did, however, discover another way to rectify the situation. If I open the table in Management Console and change something about the data type for any one column, e.g. from Char(10) to Char(11), or uncheck the Allow Nulls checkbox and then save the table the insert will work from then on.
Note: The problem is happening with a database that worked just fine under SQL Server 2000.
The error message is a generic one so there are many queries that can result in the error. In your case, it is best to use SET for variable assignments unless of course you really need to assign multiple variables from a single query at the same time or assign @@ERROR, @@ROWCOUNT in one statement. Also, please file a bug using your repro at
http://connect.microsoft.com. Thanks.
See example below:
>>
DECLARE @t_Asset TABLE
(Asset_Id INT)
INSERT INTO @t_Asset (Asset_Id) VALUES (1)
INSERT INTO @t_Asset (Asset_Id) VALUES (2)
INSERT INTO @t_Asset (Asset_Id) VALUES (3)
DECLARE @Record_Id INT
,@File_Id NVARCHAR(MAX)
,@SKP_Cust_Id NVARCHAR(MAX)
,@Unique_Barcode NVARCHAR(MAX)
SET @Record_Id = (SELECT TOP 1 Asset_Id FROM @t_Asset)
SET @File_Id = (SELECT LEFT(REPLACE(CAST(NEWID() AS NVARCHAR(50)), '-', ''), 10))
SET @Unique_Barcode=(SELECT LEFT(REPLACE(CAST(NEWID() AS NVARCHAR(50)), '-', ''), 15))
go
>>
Hi Jovo / Microsoft,
We've got the exact same error occurring both on 2005 SP1 and 'vanilla' 2005.
We've got a simple SELECT statement that includes an implicit CAST-ing between ASCII strings and UNICODE. The problem is occurring within a complex-ish hierarchy of views of several databases.
It would be quite tricky for us to reengineer the hierarchy, and we've got some concern that the problem might resurface elsewhere.
I'm sure that we'll also be logging this, but I just wondered if you came up with a solution that actually resolves the problem with the query analyser?
Thanks,
DAVE
