Good morning, I am trying to figure out, and understand, why I am receiving the error that I am when I use xml data type parameters. If you look at the proc below, I have a proc with 8 xml data type parameters. When i call this proc adn pass in the values for the parameters, I recieve is the following:
The query processor ran out of internal resources and could not produce a query plan.
This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions.
Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
In debugging, I found that I DON'T get this error if i have only 3 or 4 xml data type parameters, but if I add more than 4, I get the above error.
The proc looks like the following:
CREATE PROCEDURE dbo.dosomething
@.BegDate DateTime,
@.EndDate DateTime,
@.InClass xml,
@.InDept xml,
@.InCat xml,
@.InPayPer xml,
@.InEmp xml,
@.InLoc xml,
@.InLeave xml,
@.InSalClass xml
AS
SELECT blah, blah
FROM blah
WHERE table.column IN (select [JoinValues].[ref].value('@.id', 'int') from @.InEmp.nodes('ids/values') JoinValues([ref])
AND...join other xml datatypes just like the one above
I call the stored proc by the following...and notice that i am not passing anything very large for each value...
exec dosomething
'20060101','20060101',
N'<ids>
<values id="1" />
<values id="3" />
</ids>'
,N'<ids>
<values id="1" />
<values id="3" />
</ids>',
N'<ids>
<values id="1" />
<values id="3" />
</ids>',
N'<ids>
<values id="1" />
<values id="3" />
</ids>',
N'<ids>
<values id="1" />
<values id="3" />
</ids>',
N'<ids>
<values id="1" />
<values id="3" />
</ids>',
N'<ids>
<values id="1" />
<values id="3" />
</ids>',
N'<ids>
<values id="1" />
<values id="3" />
</ids>'
Does anyone have any suggestions as to what might be causing this? Am i doign my compare in my proc incorrectly? Any insight would be greatly appreciated...
Scott
Hi Scott
This error message normally means that you should rewrite your query. Could you please post a complete repro to either my work address (mrys at the microsoft.com address) or post it here and we will take a look.
Thanks
Michael
PS: Sorry I thought I posted this reply yesterday but my travel connectivity at the moment seems to be shakey.
|||I guess the problem is not you have lots xml data type parameters. It's caused by the WHERE clause in your query :
"WHERE table.column IN (select [JoinValues].[ref].value('@.id', 'int') from @.InEmp.nodes('ids/values') JoinValues([ref])
AND...join other xml datatypes just like the one above"
You can try to define some temp tables and shred the ID values in the xml into those tables. Jojn those temp tables in your query.
