BUG: UPDATE Using Aggregate and Arithmetic Operator Causes AV

• Microsoft SQL Server version 6.0

SYMPTOMS

In an UPDATE statement within a stored procedure, if a subquery is used to set the value of a column and includes one or more aggregate functions with arithmetic operations, and the arithmetic operation references a column, then a handled thread level access violation may occur.

WORKAROUND

One workaround is to run the UPDATE statement outside of a stored procedure. Otherwise, break up the query and assign the result of the subselect to a variable, and then use the variable in the UPDATE statement.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

For example, the following sample causes an access violation to occur:

   CREATE PROCEDURE testproc
   AS
      UPDATE   t1
      SET   a = (SELECT max (t2.b) * t3.c
         from   t2, t3
         GROUP BY t3.c)
      WHERE t1.d = 1
   go
   EXEC testproc