Microsoft KB Archive/167602

= BUG: Precision and Scale Change with SUM or AVG Functions =

PSS ID Number: 167602

Article Last Modified on 10/15/2002

-

The information in this article applies to:


 * Microsoft SQL Server 6.0
 * Microsoft SQL Server 6.5
 * Microsoft SQL Server 7.0
 * Microsoft SQL Server 2000 (all editions)

-



This article was previously published under Q167602



BUG #: 15699 (NT: 6.00) 16647 (6.50)      57616 (7.00)       224386 (SHILOH)



SYMPTOMS
If a SUM or AVG function is applied to a numeric field, the field loses its precision and scale.



WORKAROUND
Use the CONVERT function to regain the precision and scale.

For example, in a case where a cursor declared to select AVG and fetch fails with error 16923, declare the cursor like the following:

declare cur_test cursor for select convert(numeric,AVG(c1)) from table1



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
With a limited amount of testing, the SUM function seems to get a precision of 38 and retains the scale. The AVG function seems to get a precision of 38 and a scale of six if the original scale was less than six; otherwise, it retains its scale. This problem does not occur with MAX or MIN functions. You can observe this behavior with a table created with select into or with a view.

When you are trying to use a server side cursor to fetch the SUM or AVG of a numeric column, this problem causes error 16923:

Cursorfetch: Maximum length of '%s' is incompatible with that of

selected column in the cursor.

Keywords: kbBug kbDSupport kbusage KB167602

Technology: kbAudDeveloper kbSQLServ2000 kbSQLServ2000Search kbSQLServ600 kbSQLServ650 kbSQLServ700 kbSQLServSearch

-

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

© 2003 Microsoft Corporation. All rights reserved.