BUG: AV If Update on a Column with CASE and Aggregate Functions

ID: Q178442

The information in this article applies to:

Microsoft SQL Server version 6.5

BUG #: NT 17691 (6.50)

SYMPTOMS

Performing an update on a column leads to an access violation if both of the following conditions are true:

The column is updated with a CASE expression that includes aggregate functions.

-and-

The first WHEN clause is not chosen by the CASE expression.

Clients receive the following error:

This command did not return data, and it did not return any rows DB-Library Process Dead - Connection Broken

WORKAROUND

To work around this problem, do either of the following:

Place the aggregate function(s) outside the CASE expression.

-or-

Remove the WHERE clause.

For example, instead of the following query:


   update titles
   set titles.royalty = (select (case 1
               when 0 then avg(royalty)
               when 1 then avg(royalty/2)
               else avg(royalty/3)
               end)
            from roysched r
            where r.title_id = t.title_id)
   from titles t

Try the following query:


   update titles
   set titles.royalty = (select  avg(case 1
               when 0 then royalty
               when 1 then royalty/2
               else royalty/3
               end)
            from roysched r
            where r.title_id = t.title_id)
   from titles t

STATUS

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

Additional query words: AV


Keywords          : SSrvTran_SQL kbbug6.50 
Version           : WINNT:6.5
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: April 21, 1999