BUG: INSERT INTO SELECT From Derived Table Causes 803 Error

ID: Q232320

The information in this article applies to:
BUG #: 18737 (SQLBUG_65)

SYMPTOMS

An INSERT INTO ... SELECT statement, where the SELECT, selects from a derived table, may cause an 803 error:

Server: Msg 803, Level 20, State 2, Line 1
Unable to place buffer 0x0 holding logical page 368 in sdes for object
'-393' - either there is no room in sdes or buffer already in requested slot.
This error also drops the client connection to the server.


WORKAROUND

Remove the inner SELECT by first doing a SELECT INTO a temporary table. You can then SELECT from this temporary table instead of a subquery.


STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5.


MORE INFORMATION

For example, the following query causes the 803 error: 


    INSERT BenchmarkCountryWeight 
       ( benchmark,country,date,aggregation_source,weight, market_cap )
    SELECT
       benchmark, country, date, aggregation_source, weight, market_cap
    FROM
      ( SELECT 30 benchmark, xc.country, m.Business date, 
           28 aggregation_source, c.capUSD/w.capUSD weight, 
           c.capUSD * .000001 market_cap
        FROM xRawFTUnhedged c, xRawFTUnhedged w, 
           ExternalCountryMapping xc, vMonthEndDates m
        WHERE c.region = xc.name AND w.region_id = 'WORLD' AND 
           c.date = w.date AND DATEPART (yy,c.date) = m.Year AND 
           DATEPART (mm, c.date) = m.Month )
    WHERE country IS NOT NULL
To avoid this problem, you could first create a temporary table that contains the result of the inner SELECT, then use the contents of this temporary table in the outer query: 

    SELECT 30 benchmark, xc.country, m.Business date, 
       28 aggregation_source, c.capUSD/w.capUSD weight, 
       c.capUSD * .000001 market_cap<BR/>
    INTO #temp_table
    FROM xRawFTUnhedged c, xRawFTUnhedged w, 
       ExternalCountryMapping xc, vMonthEndDates m
    WHERE c.region = xc.name AND w.region_id = 'WORLD' AND 
       c.date = w.date AND DATEPART (yy,c.date) = m.Year AND 
       DATEPART (mm, c.date) = m.Month

    INSERT BenchmarkCountryWeight 
       ( benchmark,country,date,aggregation_source,weight, market_cap )
    SELECT
       benchmark, country, date, aggregation_source, weight, market_cap
    FROM
       #temp_table
    WHERE country IS NOT NULL

Additional query words: 


Keywords          : kbbug6.50 kbSQLServ650bug 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: June 11, 1999