SQL Server Performance

Floating point exception occurred

Discussion in 'General DBA Questions' started by flachance, Feb 11, 2003.

  1. flachance New Member

    I'm getting this error whenever I try to run a query against a table which contains a column with a float value. I've read some KB on MS support saying that this has been fixed, but it doesn't seem to be for me.

    I'm running SQL 2000 SP3 on Win2K SP3.

    If I don't use the float column (Quantity) in my query, it returns results. As soon as I refer to the Quantity column, I get the following:


    Server: Msg 3628, Level 16, State 1, Line 1
    A floating point exception occurred in the user process. Current transaction is canceled.

    KB 291561, which applies to SQL7, says it's been fixed and KB 295030 for SQL Server 2000 says it's been fixed in SP1.

    Anybody ever ran into this? How can I identify and fix the bad record?

    Thanks,

    Francois
  2. flachance New Member

    Well, I've found a solution to my problem. Here's what I did.

    I noticed that I could still query on the table, and it would return rows, but it would quit whenever it encountered one of the corrupted float values. My table has a unique column, PropertyID, so I did the following:


    SELECT * FROM Inventory ORDER BY PropertyID
    I check for value of PropertyID on the last record and wrote that down. I then changed the datatype for my float column (Quantity) to a varchar. I then ran the following query:


    SELECT * FROM Inventory WHERE PropertyID > 1468614
    I saw right away that one of the Quantity value was something like #QNAN. I updated all rows that had that value in Quantity to 1 and converted the column back to float.

    Voila, problem fixed. I hope that will benefit someone...

    Francois
  3. bradmcgehee New Member

    Thanks for answering your own question.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page