Article ID: 291561 - View products that this article applies to.
This article was previously published under Q291561
BUG #: 101307 (sqlbug_70)
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.
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:
301511NOTE: The following hotfix was created prior to Microsoft SQL Server 7.0 Service Pack 4.
(http://support.microsoft.com/kb/301511/EN-US/ )INF: How to Obtain the Latest SQL Server 7.0 Service Pack
The English version of this fix should have the following file attributes or later:
NOTE: Because of file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.
Version File name Platform ---------------------------------- 7.00.982 s70982i.exe Intel s70982a.exe Alpha
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.
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.
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:
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.
Divide by zero error encountered
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:
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:
Numeric value out of range
A floating point exception occurred in the user process. Current transaction is canceled
Contact us for more help
Connect with Answer Desk for expert help.