DBCC DBREINDEX fails for table

Hi,

I am facing a rather peculiar issue where I am getting a floating point exception error while rebuilding index for a particular table.

---
Error Number : 3628

Message :

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3628: [Microsoft][ODBC SQL Server Driver][SQL Server]A floating point exception occurred in the user process. Current transaction is canceled.

[Microsoft][ODBC SQL Server
----
This seems to be a rare issue ( as acknowledged by microsoft ) and they seem to suggest that it happens with SQL Server 2000 SP3 . I migrated my database into an SQL Server 2000 SP4 and started the rebuild again .. .. But it still failed with the same error ..

Am just hoping the microsoft guys are wrong and many of you have actually faced this stuff before.. Please let me know.

Thanks in advance,
Ranjit.That's one I have not come accross. Have a look at this thread, and see if you can follow the same steps:

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=70&threadid=45658&enterthread=y

Are the columns in the index float datatypes?|||What about an INDEXDEFRAG instead of a rebuild?

Regards,

hmscott|||My bet is that it is a corrupt value for a floating point number... Not all possible values are valid floating point values. Some of them are non-sensical, so any attempt to even read them produces a runtime error.

A simple test would be to do a SELECT * to see if you can successfully retrieve all of the rows.

If that is the case, you'll need to fix the row before you can successfully build the index.

-PatP|||Thanks MCrowley/Scott/Pat for all your help.
But I am not done yet and would be back after some more research.. maybe to ask you guys again or to let you what I did to overcome my issue :)

Thanks once again,
Ranjit.|||Just an update on this one ..

Well this was due to corrupt float data in the tables ..to track the errant row ..we tried to export it into a file from where we could see it .. so we tried various methods of export ..

we could not export it into an xls/textfile, As the no of rows were large, ..
export thru bcp was possible but we could not correct the data as it was in native format .. import through BCP failed surprisingly long before it encountered the corrupt data rows .. ( maybe some mismatch in the data during bcp export )

But the select statement was the best solution in identifying the corrupt rows .. SQL failed to select on the corrupt data rows .. and we knew the error lay in those rows ..

Once identified, an update command was successfully executed on the corrupt rows and rectified ..

PS: all this was done on a test database .. we still have to sit down with the business team and make the changes ..

Just wanted to know what could be the best way out

1) change the data in corrupt rows and make sure their application inputs correct data
2) alter the column of the tables to accomodate the data ..

Please let me know.

And Thanks once again for the wonderful help I recieved during this issue.

Warm Regards,
Ranjit.|||One option is to simply "plug" the offending values. If you know that column plugh of the row associated with PK 'xyzzy' is bad, you can simply:UPDATE myTable
SET plugh = 0e0
WHERE 'xyzzy' = PK

Another approach is to make a copy, the basic idea is simple, but implementing it can be a bit difficult to explain. The short answer is to copy all of the completely readable rows in the original table to a scratch table, then copy the columns you can get from the probem row or rows substituting some acceptable value for the problem columns.

The exact mechanics of this process get complicated, due to space/time/other constraints. Feel free to ask for more help if you run into problems, because there are often easy fixes for otherwise unsolvable problems if you're willing to "think outside the box" a bit!

-PatP