BUG: UPDATE Using Aggregate and Arithmetic Operator Causes AV

ID: Q152214

The information in this article applies to:

Microsoft SQL Server version 6.0

BUG#: 15062 (6.00)

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

Additional query words: Transact-SQL

Keywords : kbprg SSrvProg SSrvTran_SQL kbbug6.00
Version : 6.0
Platform : WINDOWS
Issue type : kbprb