Microsoft KB Archive/178442

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

Article ID: 178442

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q178442



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: kbbug KB178442

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.