FIX: Invalid Float Data May Lead to Floating Point Exception and Incorrect Stored Values

Article translations Article translations
Article ID: 291561 - View products that this article applies to.
This article was previously published under Q291561
BUG #: 101307 (sqlbug_70)
Expand all | Collapse all

SYMPTOMS

When the expression 0/0 (zero divided by zero) is passed to SQL Server as a floating point value for a stored procedure parameter, an EXCEPTION_INVALID_FLT_OPERATION error message may occur. Subsequent executions of the stored procedure while the procedure plan is in cache may lead to incorrect values for the parameter. If you use the parameter value to store data into the column of a table, the data may be stored incorrectly.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
301511 INF: How to Obtain the Latest SQL Server 7.0 Service Pack
NOTE: The following hotfix was created prior to Microsoft SQL Server 7.0 Service Pack 4.

The English version of this fix should have the following file attributes or later:

   Version      File name    Platform
   ----------------------------------

   7.00.982     s70982i.exe  Intel
                s70982a.exe  Alpha
				
NOTE: Because of file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.

WORKAROUND

To work around this problem, insert code into your application to avoid binding a value of 0/0 as a floating point value.

Furthermore, subsequent executions of the procedure only create problems for incorrect data if you use the same execution plan for the procedure after the exception occurs. Therefore, if you create the procedure by using the WITH RECOMPILE option, you can avoid the problem because a plan must be compiled for each execution of the procedure.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 7.0 Service Pack 4.

MORE INFORMATION

If the parameter of the procedure is used by an INSERT statement inside the procedure, the values stored in the table may be incorrect even when passing valid floating point values after the exception occurs. For example, if the exception occurs a subsequent value of 1.0 may be stored as 0.00000001

This problem only occurs if the floating point value is bound to a stored procedure parameter by an application using native SQL Server remote procedure calls (RPC). This problem does not occur if you execute the procedure as a batch with Transact-SQL because the SQL Server parser catches the incorrect floating point value. In this case, the following error message occurs:
Msg 8134
Divide by zero error encountered
The EXCEPTION_INVALID_FLT_OPERATION error message only occurs if SQL Server must perform some type of operation against the invalid floating point expression, such as a conversion. So, if the stored procedure is defined to accept a NUMERIC value but the application binds the expression 0/0 as a FLOAT value, the exception may occur.

If the SQL RPC parameter is defined to accept a FLOAT and the invalid 0/0 expression is passed to the server as FLOAT, an exception may not occur, although an incorrect float value may be used by the procedure. If the value of the parameter is stored into a table with an INSERT statement, an attempt to retrieve this data may result in an error message similar to:
Numeric value out of range
This problem does not occur in Microsoft SQL Server 2000. On a SQL Server 2000 server, the following error message is reported when you try to convert an invalid floating point expression of 0/0 to a numeric parameter:
Msg 3628
A floating point exception occurred in the user process. Current transaction is canceled

Properties

Article ID: 291561 - Last Review: October 15, 2002 - Revision: 1.0
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbbug kbfix KB291561

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com