BUG: INSERT INTO SELECT From Derived Table Causes 803 Error (232320)
The information in this article applies to:
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<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
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbBug kbpending KB232320 |
|---|
|