none
The query processor ran out of stack space during query optimization. Please simplify the query.

    Question

  • Hello
    I've got a stored procedure that receives XML as a parameter.  The XML is queried and the results are inserted into a SQL table.  The procedure has been working great, but I'm trying to pull more fields from the XML and appear to have hit some kind of limit.  I can pull 262 fields, but when I try to add more I get the error "The query processor ran out of stack space during query optimization. Please simplify the query".  If I insert dummy data into the new fields the Proc works fine, the error only occurs when I try to pull the data from the XML. 
    Any ideas on how to get around this?
    Thanks a lot.
    Eric


    I'm using SQL 2005 SP3.  My test server is a Win XP workstation SP3.  I tried applying SP3 to SQL and  increasing the maximum SQL Server memory usage setting from 1 GB to 2 GB, but it didn't make any difference.   
    Below is an abridged version of the stored proc.

    ALTER PROCEDURE [dbo].[Insert837IClaim]
    @Msg
    XML
    AS
    BEGIN
    SET NOCOUNT ON;

     

    INSERT INTO mdr.MedicalInstitutionalFlatHeader (
    [ClearingHouseID],
    [InterchangeControlNo],
    [TransactionSetControlNumber],
    [ClearingHouseTranID],
    [CreationDate],
    [ProviderName],
    [ProviderAddress],
    [ProviderCity],
    [ProviderState],
    [ProviderZip],
    [ProviderTelephone],
    [ProviderFax],
    [ProviderCountry],
    [PayToName],
    [PayToAddress],
    [PayToCity],
    on and on they go ..............
    )

    SELECT
    r.value('(SenderID/text())[1]','varchar(50)'),
    r.value('(InterchangeControlNo/text())[1]','varchar(20)'),
    r.value('(TransactionSetControlNumber/text())[1]','varchar(30)'),
    r.value('(ClearingHouseTranID/text())[1]','varchar(30)'),
    RTRIM(r.value('(CreationDate/text())[1]','varchar(20)')),
    RTRIM(r.value('(ProviderName/text())[1]','varchar(30)')),
    RTRIM(r.value('(ProviderAddress/text())[1]','varchar(30)')),
    RTRIM(r.value('(ProviderCity/text())[1]','varchar(30)')),
    RTRIM(r.value('(ProviderState/text())[1]','varchar(30)')),
    RTRIM(r.value('(ProviderZip/text())[1]','varchar(30)')),
    RTRIM(r.value('(ProviderTelephone/text())[1]','varchar(30)')),
    RTRIM(r.value('(ProviderFax/text())[1]','varchar(30)')),
    RTRIM(r.value('(ProviderCountry/text())[1]','varchar(30)')),
    RTRIM(r.value('(PayToName/text())[1]','varchar(30)')),
    RTRIM(r.value('(PayToAddress/text())[1]','varchar(30)')),
    On and on it goes until....
    I get to the 263rd field
    which is following line.  Then it bombs.
    RTRIM(r.value('(OtherProcedureCodeG/text())[1]','varchar(30)'))

    FROM
    @Msg.nodes('//Header') as d(r)




    Wednesday, March 25, 2009 1:52 PM

All replies

  • Thanks for your reply!  Unfortunately, I've already got SP3 installed and the KB article describes  a problem with SP2.  If I remember right that issue has to do with too many foreign key references, which is definately not my problem since the table I'm inserting into only has 1.
    I'm currently working around the problem by only pulling 250 fields when I perform the insert statement.  Then following with and update statement to fill in the rest of the fields.
    Thursday, April 2, 2009 10:39 AM