Microsoft KB Archive/232320

= BUG: INSERT INTO SELECT From Derived Table Causes 803 Error =

Article ID: 232320

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q232320



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 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 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

Keywords: kbbug kbpending KB232320

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.