Friday, February 17, 2012

Error selecting data due to floating pt exception

Got a good one! or a bad one depending if the boss is breathing down the neck.

I am selecting from a table. When certain columns are selected, sql server generates the following message:

[Microsoft][ODBC SQL Server Driver]Numeric value out of range

To be more specific, if i select the id column of the table I get perhaps 4 rows.

select id from table
1
2
3
4

If I select the id column plus the column causing problems, then I only see the rows that don't generate the message, plus i get the above listed message

select id, columnThatIsFloat from table
1

In fact there are 4 columns in the table that are generating these messages. All of which are of type Float.

If I try to convert the column I get the following error:
Server: Msg 3628, Level 16, State 1, Line 1
A floating point exception occurred in the user process. Current transaction is canceled.

What could cause this. Bad data? How could it have been inputted? Is this a bug with SQL 2k? Why me?

SQL information:
Microsoft SQL Server 2000 - 8.00.384 (Intel X86) May 23 2001 00:02:52 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)Are you aggregating any of the columns ? Have you run dbcc checktable or dbreindex ?|||Which service pack do you have installed for sql server ?|||I am doing no aggregation. It truely is as simple as

select colA, colB from table

SQL information:
Microsoft SQL Server 2000 - 8.00.384 (Intel X86) May 23 2001 00:02:52 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Originally posted by rnealejr
Which service pack do you have installed for sql server ?|||The problem with posting SQL Server 2000 - 8.00.384 it does not tell you what service pack sql server has installed. In your case you have sp1 installed. I would recommend that you update your service pack.

Also, have you run the dbcc commands ?|||Hello,

I am not aggregating columns (see the previous message) and I have run dbcc checktable with the following result:

DBCC results for 'TABLENAME'.
There are 5218 rows in 62 pages for object 'TABLENAME'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I do not know what "dbreindex" is.

Thanks
Josh

Originally posted by rnealejr
Are you aggregating any of the columns ? Have you run dbcc checktable or dbreindex ?|||There is a bug with the indexing wizard that is corrected when you update the service pack that causes similar issues that you are experiencing. dbcc dbreindex rebuilds an index for a table.|||I've now upgraded to sp3a. I was looking at the service pack for the Win2K server. My oversight. Unfortunately I am still having the exact same problem.

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition
on Windows NT 5.0 (Build 2195: Service Pack 4)

i did run "dbcc checktable" and "dbcc dbreindex"

If you have any more ideas I would greatly appreciate.

Thanks alot

Originally posted by rnealejr
The problem with posting SQL Server 2000 - 8.00.384 it does not tell you what service pack sql server has installed. In your case you have sp1 installed. I would recommend that you update your service pack.

Also, have you run the dbcc commands ?

No comments:

Post a Comment