BUG: Floating point underflow exceptions may occur with data below the minimum float exponent (273557)


The information in this article applies to:

  • Microsoft SQL Server 7.0
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 (<column> < -2.23E-308 OR <column> = 0.0 OR <column> > 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 = "' + @table + N'", column_name = "' + @column + N'"'
  EXECUTE (@cmd)
  FETCH NEXT FROM colcursor INTO @table, @column
END
DEALLOCATE colcursor
Modification Type:MinorLast Reviewed:9/27/2004
Keywords:kbBug kbCodeSnippet kbpending KB273557
©2004 Microsoft Corporation. All rights reserved.