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

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.

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