Microsoft KB Archive/135588

{|
 * width="100%"|

FIX: SELECT INTO w/ Correlated Subqueries May Cause AV

 * }

Q135588

-

The information in this article applies to:


 * Microsoft SQL Server version 6.0

-

BUG# NT: 11058 (6.00)

SYMPTOMS
In Microsoft SQL Server version 6.0, a SELECT INTO statement that includes a correlated subquery using the aggregate functions SUM or AVG may cause a client access violation (AV).

CAUSE
The specific conditions under which this problem occurs are:


 * SELECT INTO statement /*the SELECT works fine without INTO clause*/
 * Correlated subquery in the 
 * SUM or AVG in the correlated subquery /* the statement works fine with MIN and MAX */

WORKAROUND
The correlated subquery works when MAX or MIN is the aggregate function. However, when the AVG or SUM functions are used, then you should not use the correlated query, but split the query and use GROUP BY in the first query, get those results, and join with the tables in the second query to achieve the same results as in the correlated subquery.

STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem was corrected in Service Pack 1 for SQL Server version 6.0. For more information, contact your primary support provider.

Example Scenario
The following script reproduces the client AV:

  use master go  sp_dboption pubs,'select',true go  use pubs go  checkpoint go  create table t11 ( col1 int,    col2 int ) go  create table t22 (col1 int ) go  insert into t11 values (1,2) go  insert into t11 values (3,4) go  insert into t11 values (2,5) go  insert into t11 values (1,5) go  insert into t22 values (1) go  insert into t22 values (2) go  /* case 1: using a temporary table in INTO clause */ select t22.col1,(select sum(col2) from t11 where col1 = t22.col1) into #temp_table from t22 go  /* case 2: using a table in user database in the  INTO clause */ select t22.col1,(select sum(col2) from t11 where col1 = t22.col1) into userdb_table from t22 go  /* case 3: using AVG in subquery */ select t22.col1,(select avg(col2) from t11 where col1 = t22.col1) into #temp_table from t22 go Additional query words: sql6 temporary work table

Keywords : kbprogramming

Issue type : kbbug

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600