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

[et_pb_section bb_built=”1″][et_pb_row][et_pb_column type=”4_4″][et_pb_text _builder_version=”3.0.106″ background_layout=”light”]

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: –

[/et_pb_text][et_pb_dmb_code_snippet _builder_version=”3.0.106″ style=”default” linenums=”off” usetabwidth=”off”]Q1JFQVRFIEZVTkNUSU9OIElzTmFOKApAbnVtYmVyIGZsb2F0CikKUkVUVVJOUyBpbnQKQVMKCUJFR0lOCgkJcmV0dXJuIGNhc2UgaXNudW1lcmljKGNvbnZlcnQodmFyY2hhcigxMDApLCBjb2FsZXNjZShAbnVtYmVyLDApKSkgd2hlbiAxIHRoZW4gMCBlbHNlIDEgZW5kCglFTkQKR08=[/et_pb_dmb_code_snippet][/et_pb_column][/et_pb_row][/et_pb_section]