SQL Server 2000 - A floating point exception occurred in the user process

Problem

The following error "A floating point exception occurred in the user process. Current transaction is canceled." is generated by SQL Server 2000 when invalid values get into float columns.

The error may occur in Enterprise Manager and Query Analyser whenever the affected column is used in a WHERE or ORDER BY clause. This makes it very difficult to fix up the affected records as you can't select them with any kind of select or update query. If there aren't too many of them and you can identify them, you can update them by selecting on a different column, but typically it isn't that easy.

It is possible to export the data (to Access perhaps), where the data can be fixed, and re-importing.

We finally found a solution here http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d8a8b650-2fd1-40f3-9819-13a98fe19b94, which worked perfectly for us, and is reproduced below: -

Solution

Create a user-defined function to identify the effected rows: -

CREATE FUNCTION IsNaN( @number float ) RETURNS int AS BEGIN return case isnumeric(convert(varchar(100), coalesce(@number,0))) when 1 then 0 else 1 end END GO

Use this in an UPDATE query to fix up the affected rows: -

UPDATE mytable SET mycolumn=0 WHERE dbo.IsNan(mycolumn) = 1