Floating Point Exception in SQL Server 2000

Hi,

I got below error in the SQL Server Production Server and i checked in the microsoft site it needs to install SQL Server service pack 4 to resolve the
problem.

"A floating point exception occurred in the user process. Current transaction is canceled"

I need help that i want to reproduce this below problem in the SQL Server environment and tried several ways but no luck.

Please advise me how to reproduce the problem.

Would be appreciate your help.

Regards
SathishFor what cause you are trying to do that? Check this ...Link (http://www.dbforums.com/showthread.php?t=318196)|||Wants to check after installing service pack 4. so that we can confirm it should not happen in future.
Any clues to reprodue it.

Regards
Sathish|||Wants to check after installing service pack 4. so that we can confirm it should not happen in future.
Any clues to reprodue it.

Regards
Sathish
FYI...
If the following conditions are all true, Microsoft SQL Server may store floating point data with an exponent lower than -308, which may cause floating point underflow exceptions that terminate a clients connection to SQL Server:

• The client application is using stored procedures or server side cursors to perform data modification
and passes the request to the SQL Server server as a remote procedure call (RPC) event.
• The client application is passing parameters for the RPC event to the SQL Server server.
• The column of the table affected by the parameter that is passed is defined as a float datatype.
• If a stored procedure is called, the parameter is defined as a float datatype.

And plz check the previous link that I gave you...You could get you answer there.|||Joydeep,

Thanks for your information. I have tried following ways to reproduce it

1. By using Index Tuning Wizard Execution
2. By passing the expression 0/0 (zero divided by zero) to SQL Server as a floating point value for a stored procedure parameter
3. By trying query with aggregate function
4. By running a Complex Query
5. By Query optimization

But i couldnt able to reproduce it. Do you have any stored procedure or SQL Query to stimulate this problem.

Regards
Sathishsql

Float Data Type for Money

Hi,
I'm now supporting a production database that uses the float data type to
store monetary values in one of the tables. No this is not my design but I
am required to support it and to generate reports for the data. I
understand that the float/real data types round incorrectly. How can I
round the data correctly? Here is an sample float value:
1139.3099999999999
I need 1139.31.
Thanks
JerryI also need to have any trailing zeros removed as well.
Thanks
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OCxn4jjvFHA.908@.tk2msftngp13.phx.gbl...
> Hi,
> I'm now supporting a production database that uses the float data type to
> store monetary values in one of the tables. No this is not my design but
> I am required to support it and to generate reports for the data. I
> understand that the float/real data types round incorrectly. How can I
> round the data correctly? Here is an sample float value:
> 1139.3099999999999
> I need 1139.31.
> Thanks
> Jerry
>|||This seemed to work:
convert(decimal(10,2),round(convert(mone
y,column),2)) --column is the
float column
Is this ok or...?
Thanks
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:emIDnmjvFHA.3400@.TK2MSFTNGP14.phx.gbl...
>I also need to have any trailing zeros removed as well.
> Thanks
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OCxn4jjvFHA.908@.tk2msftngp13.phx.gbl...
>|||You cannot support it. It will not work, thanks to floating point
rounding errors. It is also illegal in the EU and in violation of GAAP
in the United States. Under SOX, there is a good chance that your boss
is going to jail for this kind of accounting. I would update my resume
and send the boss a letter so that you do not get caught up in the mess
that is coming.
Oh, the stinking, dirty, unusable kludge is CAST() and/or ROUND(). Do
not convert to MONEY -- it is proprietary and has funny math.|||--CELKO-- wrote:
> You cannot support it. It will not work, thanks to floating point
> rounding errors. It is also illegal in the EU and in violation of GAAP
> in the United States. Under SOX, there is a good chance that your boss
> is going to jail for this kind of accounting. I would update my resume
> and send the boss a letter so that you do not get caught up in the mess
> that is coming.
> Oh, the stinking, dirty, unusable kludge is CAST() and/or ROUND(). Do
> not convert to MONEY -- it is proprietary and has funny math.
Hi Joe,
Please, please, please, can you post a reference to the illegality of
this in the EU? I can find *nothing* online (other than Euro
*conversion* rules), and We're about to have a new system introduced
here that uses floating point all over the place for currency, so if
you could provide a reference, I might be able to force a change to the
system.
Damien|||Thanks Joe.
Scary thing is it's an accounting-based software package. Yeah I noticed
this and about 5 other "Why did you...?" yesterday afternoon when I was
meeting with the vendor. I'll follow up with the vendor, management and
accounting today. Do you have any links that support the violation that I
can forward on?
For the reports I'll be generating in RS for account aging, what would the
kludge code look like to round to 2 decimal places and trucate trailing
zeros? Here is the code I came up with:
convert(decimal(10,2),round(convert(mone
y,column),2))
Thanks
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127274857.269620.320950@.g43g2000cwa.googlegroups.com...
> You cannot support it. It will not work, thanks to floating point
> rounding errors. It is also illegal in the EU and in violation of GAAP
> in the United States. Under SOX, there is a good chance that your boss
> is going to jail for this kind of accounting. I would update my resume
> and send the boss a letter so that you do not get caught up in the mess
> that is coming.
> Oh, the stinking, dirty, unusable kludge is CAST() and/or ROUND(). Do
> not convert to MONEY -- it is proprietary and has funny math.
>|||On Wed, 21 Sep 2005 08:46:44 -0700, Jerry Spivey wrote:
(snip)
>For the reports I'll be generating in RS for account aging, what would the
>kludge code look like to round to 2 decimal places and trucate trailing
>zeros? Here is the code I came up with:
> convert(decimal(10,2),round(convert(mone
y,column),2))
Hi Jerry,
No need to make it that complicated.
SELECT CONVERT(decimal(10,2), BadlyTypedColumn)
will do.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Overcomplicating things again...damn! ;-)
Thanks Hugo
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:p8i3j1p1g9bcicg3doqjdpca4qocv534dp@.
4ax.com...
> On Wed, 21 Sep 2005 08:46:44 -0700, Jerry Spivey wrote:
> (snip)
> Hi Jerry,
> No need to make it that complicated.
> SELECT CONVERT(decimal(10,2), BadlyTypedColumn)
> will do.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is a quick "cut & paste":
The MONEY datatype has rounding errors. Using more than one operation
(multiplication or division) on money columns will produce severe
rounding errors. A simple way to visualize money arithmetic is to place
a ROUND() function calls after every operation. For example,
Amount = (Portion / total_amt) * gross_amt
can be rewritten using money arithmetic as:
Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)
Rounding to four decimal places might not seem an issue, until the
numbers you are using are greater than 10,000.
BEGIN
DECLARE @.gross_amt MONEY,
@.total_amt MONEY,
@.my_part MONEY,
@.money_result MONEY,
@.float_result FLOAT,
@.all_floats FLOAT;
SET @.gross_amt = 55294.72;
SET @.total_amt = 7328.75;
SET @.my_part = 1793.33;
SET @.money_result = (@.my_part / @.total_amt) * @.gross_amt;
SET @.float_result = (@.my_part / @.total_amt) * @.gross_amt;
SET @.Retult3 = (CAST(@.my_part AS FLOAT)
/ CAST( @.total_amt AS FLOAT))
* CAST(FLOAT, @.gross_amtAS FLOAT);
SELECT @.money_result, @.float_result, @.all_floats;
END;
@.money_result = 13525.09 -- incorrect
@.float_result = 13525.0885 -- incorrect
@.all_floats = 13530.5038673171 -- correct, with a -5.42 error|||Thanks Joe!
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127417068.157889.200980@.g14g2000cwa.googlegroups.com...
> Here is a quick "cut & paste":
> The MONEY datatype has rounding errors. Using more than one operation
> (multiplication or division) on money columns will produce severe
> rounding errors. A simple way to visualize money arithmetic is to place
> a ROUND() function calls after every operation. For example,
> Amount = (Portion / total_amt) * gross_amt
> can be rewritten using money arithmetic as:
> Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)
> Rounding to four decimal places might not seem an issue, until the
> numbers you are using are greater than 10,000.
> BEGIN
> DECLARE @.gross_amt MONEY,
> @.total_amt MONEY,
> @.my_part MONEY,
> @.money_result MONEY,
> @.float_result FLOAT,
> @.all_floats FLOAT;
> SET @.gross_amt = 55294.72;
> SET @.total_amt = 7328.75;
> SET @.my_part = 1793.33;
> SET @.money_result = (@.my_part / @.total_amt) * @.gross_amt;
> SET @.float_result = (@.my_part / @.total_amt) * @.gross_amt;
> SET @.Retult3 = (CAST(@.my_part AS FLOAT)
> / CAST( @.total_amt AS FLOAT))
> * CAST(FLOAT, @.gross_amtAS FLOAT);
> SELECT @.money_result, @.float_result, @.all_floats;
> END;
> @.money_result = 13525.09 -- incorrect
> @.float_result = 13525.0885 -- incorrect
> @.all_floats = 13530.5038673171 -- correct, with a -5.42 error
>

Flipping back to simple mode

Hi,
I have three user databases on my production server and I noticed a strange
behavior whenever the server gets rebooted. on two of the databases the
recovery model flips back from Full to simple when the server is rebooted
any time. On the third database the recover model stays as Full even after
the reboot. Even after I switch the recovery model back to full on the two
databases what I see is, as soon as the server gets rebooted, they both flip
back to simple.
I am confused as to why it is happening only with two databases and not the
third. Is there something that is set up wrongly on these two databases?.
Any insight is highly helpful.
Thanks
MeherCheck under Management | jobs in Enterprise Manager, something this is
running sp_dboption '<database','select into','true' and sp_dboption
'<database>','trunc','true'
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have three user databases on my production server and I noticed a
strange
> behavior whenever the server gets rebooted. on two of the databases the
> recovery model flips back from Full to simple when the server is rebooted
> any time. On the third database the recover model stays as Full even after
> the reboot. Even after I switch the recovery model back to full on the two
> databases what I see is, as soon as the server gets rebooted, they both
flip
> back to simple.
> I am confused as to why it is happening only with two databases and not
the
> third. Is there something that is set up wrongly on these two databases?.
> Any insight is highly helpful.
> Thanks
> Meher
>|||I checked them. No job is running with those options. I only have the
following options set to true on both the databases.
torn page detection
auto create statistics
auto update statistics
BTW I am running SQL Server 2000 with SP3 Installed.
Thanks for the suggestion Andy.
Meher
"Andy Svendsen" <andymcdba1@.NOMORESPAM.yahoo.com> wrote in message
news:ubIupIW$DHA.212@.TK2MSFTNGP12.phx.gbl...
> Check under Management | jobs in Enterprise Manager, something this is
> running sp_dboption '<database','select into','true' and sp_dboption
> '<database>','trunc','true'
> --
> ****************************************
***************************
> Andy S.
> MCSE NT/2000, MCDBA SQL 7/2000
> andymcdba1@.NOMORESPAM.yahoo.com
> Please remove NOMORESPAM before replying.
> Always keep your antivirus and Microsoft software
> up to date with the latest definitions and product updates.
> Be suspicious of every email attachment, I will never send
> or post anything other than the text of a http:// link nor
> post the link directly to a file for downloading.
> This posting is provided "as is" with no warranties
> and confers no rights.
> ****************************************
***************************
> "Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
> news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> strange
rebooted
after
two
> flip
> the
databases?.
>

Flipping back to simple mode

Hi,
I have three user databases on my production server and I noticed a strange
behavior whenever the server gets rebooted. on two of the databases the
recovery model flips back from Full to simple when the server is rebooted
any time. On the third database the recover model stays as Full even after
the reboot. Even after I switch the recovery model back to full on the two
databases what I see is, as soon as the server gets rebooted, they both flip
back to simple.
I am confused as to why it is happening only with two databases and not the
third. Is there something that is set up wrongly on these two databases?.
Any insight is highly helpful.
Thanks
MeherCheck under Management | jobs in Enterprise Manager, something this is
running sp_dboption '<database','select into','true' and sp_dboption
'<database>','trunc','true'
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have three user databases on my production server and I noticed a
strange
> behavior whenever the server gets rebooted. on two of the databases the
> recovery model flips back from Full to simple when the server is rebooted
> any time. On the third database the recover model stays as Full even after
> the reboot. Even after I switch the recovery model back to full on the two
> databases what I see is, as soon as the server gets rebooted, they both
flip
> back to simple.
> I am confused as to why it is happening only with two databases and not
the
> third. Is there something that is set up wrongly on these two databases?.
> Any insight is highly helpful.
> Thanks
> Meher
>|||I checked them. No job is running with those options. I only have the
following options set to true on both the databases.
torn page detection
auto create statistics
auto update statistics
BTW I am running SQL Server 2000 with SP3 Installed.
Thanks for the suggestion Andy.
Meher
"Andy Svendsen" <andymcdba1@.NOMORESPAM.yahoo.com> wrote in message
news:ubIupIW$DHA.212@.TK2MSFTNGP12.phx.gbl...
> Check under Management | jobs in Enterprise Manager, something this is
> running sp_dboption '<database','select into','true' and sp_dboption
> '<database>','trunc','true'
> --
> *******************************************************************
> Andy S.
> MCSE NT/2000, MCDBA SQL 7/2000
> andymcdba1@.NOMORESPAM.yahoo.com
> Please remove NOMORESPAM before replying.
> Always keep your antivirus and Microsoft software
> up to date with the latest definitions and product updates.
> Be suspicious of every email attachment, I will never send
> or post anything other than the text of a http:// link nor
> post the link directly to a file for downloading.
> This posting is provided "as is" with no warranties
> and confers no rights.
> *******************************************************************
> "Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
> news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have three user databases on my production server and I noticed a
> strange
> > behavior whenever the server gets rebooted. on two of the databases the
> > recovery model flips back from Full to simple when the server is
rebooted
> > any time. On the third database the recover model stays as Full even
after
> > the reboot. Even after I switch the recovery model back to full on the
two
> > databases what I see is, as soon as the server gets rebooted, they both
> flip
> > back to simple.
> >
> > I am confused as to why it is happening only with two databases and not
> the
> > third. Is there something that is set up wrongly on these two
databases?.
> > Any insight is highly helpful.
> >
> > Thanks
> >
> > Meher
> >
> >
>