Microsoft KB Archive/273557

= BUG: Floating point underflow exceptions may occur with data below the minimum float exponent =

Article ID: 273557

Article Last Modified on 9/27/2004

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q273557



BUG #: 58359 (SQLBUG_70)



SYMPTOMS
If the following conditions are all true, Microsoft SQL Server may store floating point data with an exponent lower than -308, which may cause floating point underflow exceptions that terminate a clients connection to SQL Server:
 * The client application is using stored procedures or server side cursors to perform data modification and passes the request to the SQL Server server as a remote procedure call (RPC) event.
 * The client application is passing parameters for the RPC event to the SQL Server server.
 * The column of the table affected by the parameter that is passed is defined as a float datatype.
 * If a stored procedure is called, the parameter is defined as a float datatype.



WORKAROUND
Correct existing data and create a check constraint to validate the float column data as follows to prevent the minimum exponent from being stored in the SQL Server server: CHECK ( < -2.23E-308 OR = 0.0 OR > 2.23E-308)



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.

This is not an issue in SQL Server 2000.



MORE INFORMATION
The Institute of Electrical and Electronics Engineers (IEEE) specification for floating point numbers defines C/C++ double type (float datatype in SQL Server) to have an approximate range of 2.2 E –308 to 1.8 E +308. However, you can use the mantissa to store part of the exponent, hence the approximate minimum value greater than 0 (zero) that you can use is 1 E +323. When these extremely small numbers are stored in SQL Server, various floating point underflow exceptions may occur. Here is a query that you can use to search for these values in a database. If data is found with the minimum exponent or lower, it reports the entire row with minimum or lower exponents from that table, and then reports the table and column name in which the minimum or lower exponent data exists.

WARNING: Because this possibly generates multiple table scans, you should use this query with caution in large databases due to possible performance impact. DECLARE @table nvarchar(128) DECLARE @column nvarchar(128) DECLARE @cmd nvarchar(4000) DECLARE colcursor CURSOR FOR SELECT table_name, column_name FROM inFORmation_schema.columns WHERE data_type = 'float' OPEN colcursor FETCH NEXT FROM colcursor INTO @table, @column WHILE @@fetch_status <> -1 BEGIN SELECT @cmd = N'SELECT * FROM [' + @table + N'] WHERE ([' + @column + N'] > 0 AND [' + @column + N'] < 2.23E-308) OR ([' + @column + N'] < 0 AND [' + @column + N'] > -2.23E-308) IF @@rowcount > 0 SELECT table_name = &quot;' + @table + N'&quot;, column_name = &quot;' + @column + N'&quot;' EXECUTE (@cmd) FETCH NEXT FROM colcursor INTO @table, @column END DEALLOCATE colcursor

Keywords: kbbug kbcodesnippet kbpending KB273557

-

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

© Microsoft Corporation. All rights reserved.