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

• Microsoft SQL Server version 6.5

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.

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.

   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 

   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