floating point truncation

How can I truncate a floating point number to required number of decimal points
Eg:
100.642364074 to 100.64 and 67.643929847 to 67.645
Thanks.STR|||...And the last number should be rounded to 67.644 not 67.645

Floating point precision

Hi, I have a SP which adds a bunch of the same number together 365
times
The base number is 0.0575342465753425
I am storing this as a float but when I view it in the first iteration
it appears as
0.0575342
The next one is
0.115068
and the 10th one is
0.575342
and finally the 365 one is
21.0575
However if I multiply the original number by 365 I get the following
21.0000000000000125
Which is vastly different from the one I got using a float.
How can I get more precision using MSSQL float? Am I using the wrong
Datatype'
TIA
Mark
================================= 2006MJGOOGLENEWSDon't use FLOAT it is not accurate in terms of precision
What you get if you use DECIMAL datatype intead?
<MarkusJNZ@.gmail.com> wrote in message
news:1159441537.669380.150810@.b28g2000cwb.googlegroups.com...
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================> 2006MJGOOGLENEWS
>|||MarkusJNZ@.gmail.com wrote:
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================> 2006MJGOOGLENEWS
>
From Books Online:
"Floating point data is approximate; not all values in the data type
range can be precisely represented."
Float is not a precise data type, use DECIMAL or one of the other
numeric types instead...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>Hi, I have a SP which adds a bunch of the same number together 365
>times
>The base number is 0.0575342465753425
>I am storing this as a float but when I view it in the first iteration
>it appears as
>0.0575342
>The next one is
>0.115068
>and the 10th one is
>0.575342
>and finally the 365 one is
>21.0575
>However if I multiply the original number by 365 I get the following
>21.0000000000000125
>Which is vastly different from the one I got using a float.
>How can I get more precision using MSSQL float? Am I using the wrong
>Datatype'
Hi Mark,
Nothing wrong with the datatype - the pproblem is in the code. Since the
result is off by 0.0575, which is exactly the number you start with, I'd
double-check the code - you're probably adding the same number 366 times
instead of 365 times.
Here's some code I used (note the CAST near the end to force display of
numbers to the far right of the decimal point):
declare @.flt float, @.res float, @.i int
set @.flt = 0.0575342465753425
set @.res = 0
set @.i = 0
while @.i < 365
begin
set @.i = @.i + 1
set @.res = @.res + @.flt
end
select cast(@.res as decimal(38,30))
select cast(@.flt * 365.0 as decimal(38,30))
Results:
---
21.000000000000046000000000000000
---
21.000000000000014000000000000000
As you see, there is SOME loss of precision, but not quites as much as
you had.
Incidentally, if you change the datatypes of @.flt and @.res in the code
above to decimal(38,10), the results change to
---
21.000000000000012500000000000000
---
21.000000000000012500000000000000
Hugo Kornelis, SQL Server MVP|||Thanks everyone for your help.
Hug, you were right, I was adding it 1 more than I needed to; late
night programming lol
Thanks
Mark
Hugo Kornelis wrote:
> On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
> >Hi, I have a SP which adds a bunch of the same number together 365
> >times
> >
> >The base number is 0.0575342465753425
> >
> >I am storing this as a float but when I view it in the first iteration
> >it appears as
> >
> >0.0575342
> >
> >The next one is
> >
> >0.115068
> >
> >and the 10th one is
> >
> >0.575342
> >
> >and finally the 365 one is
> >
> >21.0575
> >
> >However if I multiply the original number by 365 I get the following
> >21.0000000000000125
> >
> >Which is vastly different from the one I got using a float.
> >
> >How can I get more precision using MSSQL float? Am I using the wrong
> >Datatype'
> Hi Mark,
> Nothing wrong with the datatype - the pproblem is in the code. Since the
> result is off by 0.0575, which is exactly the number you start with, I'd
> double-check the code - you're probably adding the same number 366 times
> instead of 365 times.
> Here's some code I used (note the CAST near the end to force display of
> numbers to the far right of the decimal point):
> declare @.flt float, @.res float, @.i int
> set @.flt = 0.0575342465753425
> set @.res = 0
> set @.i = 0
> while @.i < 365
> begin
> set @.i = @.i + 1
> set @.res = @.res + @.flt
> end
> select cast(@.res as decimal(38,30))
> select cast(@.flt * 365.0 as decimal(38,30))
> Results:
>
> ---
> 21.000000000000046000000000000000
>
> ---
> 21.000000000000014000000000000000
> As you see, there is SOME loss of precision, but not quites as much as
> you had.
> Incidentally, if you change the datatypes of @.flt and @.res in the code
> above to decimal(38,10), the results change to
>
> ---
> 21.000000000000012500000000000000
>
> ---
> 21.000000000000012500000000000000
>
> --
> Hugo Kornelis, SQL Server MVPsql

Floating point precision

Hi, I have a SP which adds a bunch of the same number together 365
times
The base number is 0.0575342465753425
I am storing this as a float but when I view it in the first iteration
it appears as
0.0575342
The next one is
0.115068
and the 10th one is
0.575342
and finally the 365 one is
21.0575
However if I multiply the original number by 365 I get the following
21.0000000000000125
Which is vastly different from the one I got using a float.
How can I get more precision using MSSQL float? Am I using the wrong
Datatype?
TIA
Mark
=================================
2006MJGOOGLENEWS
Don't use FLOAT it is not accurate in terms of precision
What you get if you use DECIMAL datatype intead?
<MarkusJNZ@.gmail.com> wrote in message
news:1159441537.669380.150810@.b28g2000cwb.googlegr oups.com...
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype?
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>
|||MarkusJNZ@.gmail.com wrote:
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype?
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>
From Books Online:
"Floating point data is approximate; not all values in the data type
range can be precisely represented."
Float is not a precise data type, use DECIMAL or one of the other
numeric types instead...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:

>Hi, I have a SP which adds a bunch of the same number together 365
>times
>The base number is 0.0575342465753425
>I am storing this as a float but when I view it in the first iteration
>it appears as
>0.0575342
>The next one is
>0.115068
>and the 10th one is
>0.575342
>and finally the 365 one is
>21.0575
>However if I multiply the original number by 365 I get the following
>21.0000000000000125
>Which is vastly different from the one I got using a float.
>How can I get more precision using MSSQL float? Am I using the wrong
>Datatype?
Hi Mark,
Nothing wrong with the datatype - the pproblem is in the code. Since the
result is off by 0.0575, which is exactly the number you start with, I'd
double-check the code - you're probably adding the same number 366 times
instead of 365 times.
Here's some code I used (note the CAST near the end to force display of
numbers to the far right of the decimal point):
declare @.flt float, @.res float, @.i int
set @.flt = 0.0575342465753425
set @.res = 0
set @.i = 0
while @.i < 365
begin
set @.i = @.i + 1
set @.res = @.res + @.flt
end
select cast(@.res as decimal(38,30))
select cast(@.flt * 365.0 as decimal(38,30))
Results:
21.000000000000046000000000000000
21.000000000000014000000000000000
As you see, there is SOME loss of precision, but not quites as much as
you had.
Incidentally, if you change the datatypes of @.flt and @.res in the code
above to decimal(38,10), the results change to
21.000000000000012500000000000000
21.000000000000012500000000000000
Hugo Kornelis, SQL Server MVP
|||Thanks everyone for your help.
Hug, you were right, I was adding it 1 more than I needed to; late
night programming lol
Thanks
Mark
Hugo Kornelis wrote:
> On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>
> Hi Mark,
> Nothing wrong with the datatype - the pproblem is in the code. Since the
> result is off by 0.0575, which is exactly the number you start with, I'd
> double-check the code - you're probably adding the same number 366 times
> instead of 365 times.
> Here's some code I used (note the CAST near the end to force display of
> numbers to the far right of the decimal point):
> declare @.flt float, @.res float, @.i int
> set @.flt = 0.0575342465753425
> set @.res = 0
> set @.i = 0
> while @.i < 365
> begin
> set @.i = @.i + 1
> set @.res = @.res + @.flt
> end
> select cast(@.res as decimal(38,30))
> select cast(@.flt * 365.0 as decimal(38,30))
> Results:
>
> 21.000000000000046000000000000000
>
> 21.000000000000014000000000000000
> As you see, there is SOME loss of precision, but not quites as much as
> you had.
> Incidentally, if you change the datatypes of @.flt and @.res in the code
> above to decimal(38,10), the results change to
>
> 21.000000000000012500000000000000
>
> 21.000000000000012500000000000000
>
> --
> Hugo Kornelis, SQL Server MVP

Floating point precision

Hi, I have a SP which adds a bunch of the same number together 365
times
The base number is 0.0575342465753425
I am storing this as a float but when I view it in the first iteration
it appears as
0.0575342
The next one is
0.115068
and the 10th one is
0.575342
and finally the 365 one is
21.0575
However if I multiply the original number by 365 I get the following
21.0000000000000125
Which is vastly different from the one I got using a float.
How can I get more precision using MSSQL float? Am I using the wrong
Datatype'
TIA
Mark
=================================
2006MJGOOGLENEWSDon't use FLOAT it is not accurate in terms of precision
What you get if you use DECIMAL datatype intead?
<MarkusJNZ@.gmail.com> wrote in message
news:1159441537.669380.150810@.b28g2000cwb.googlegroups.com...
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>|||MarkusJNZ@.gmail.com wrote:
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>
From Books Online:
"Floating point data is approximate; not all values in the data type
range can be precisely represented."
Float is not a precise data type, use DECIMAL or one of the other
numeric types instead...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:

>Hi, I have a SP which adds a bunch of the same number together 365
>times
>The base number is 0.0575342465753425
>I am storing this as a float but when I view it in the first iteration
>it appears as
>0.0575342
>The next one is
>0.115068
>and the 10th one is
>0.575342
>and finally the 365 one is
>21.0575
>However if I multiply the original number by 365 I get the following
>21.0000000000000125
>Which is vastly different from the one I got using a float.
>How can I get more precision using MSSQL float? Am I using the wrong
>Datatype'
Hi Mark,
Nothing wrong with the datatype - the pproblem is in the code. Since the
result is off by 0.0575, which is exactly the number you start with, I'd
double-check the code - you're probably adding the same number 366 times
instead of 365 times.
Here's some code I used (note the CAST near the end to force display of
numbers to the far right of the decimal point):
declare @.flt float, @.res float, @.i int
set @.flt = 0.0575342465753425
set @.res = 0
set @.i = 0
while @.i < 365
begin
set @.i = @.i + 1
set @.res = @.res + @.flt
end
select cast(@.res as decimal(38,30))
select cast(@.flt * 365.0 as decimal(38,30))
Results:
---
21.000000000000046000000000000000
---
21.000000000000014000000000000000
As you see, there is SOME loss of precision, but not quites as much as
you had.
Incidentally, if you change the datatypes of @.flt and @.res in the code
above to decimal(38,10), the results change to
---
21.000000000000012500000000000000
---
21.000000000000012500000000000000
Hugo Kornelis, SQL Server MVP|||Thanks everyone for your help.
Hug, you were right, I was adding it 1 more than I needed to; late
night programming lol
Thanks
Mark
Hugo Kornelis wrote:
> On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>
> Hi Mark,
> Nothing wrong with the datatype - the pproblem is in the code. Since the
> result is off by 0.0575, which is exactly the number you start with, I'd
> double-check the code - you're probably adding the same number 366 times
> instead of 365 times.
> Here's some code I used (note the CAST near the end to force display of
> numbers to the far right of the decimal point):
> declare @.flt float, @.res float, @.i int
> set @.flt = 0.0575342465753425
> set @.res = 0
> set @.i = 0
> while @.i < 365
> begin
> set @.i = @.i + 1
> set @.res = @.res + @.flt
> end
> select cast(@.res as decimal(38,30))
> select cast(@.flt * 365.0 as decimal(38,30))
> Results:
>
> ---
> 21.000000000000046000000000000000
>
> ---
> 21.000000000000014000000000000000
> As you see, there is SOME loss of precision, but not quites as much as
> you had.
> Incidentally, if you change the datatypes of @.flt and @.res in the code
> above to decimal(38,10), the results change to
>
> ---
> 21.000000000000012500000000000000
>
> ---
> 21.000000000000012500000000000000
>
> --
> Hugo Kornelis, SQL Server MVP

Floating Point Numbers in BCP File

Hi,
I am BCPing in a tab-delimited text file and I am getting this error
message:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
The text file records look like below:
200509 67195 M12AB00 67195 SSL TEST RECORD 7217 240.41
200509 67338 DNMXAED 67338 CTA TEST RECORD 50 237.5
Not sure if that floating point number at the end of the file is the cause
of this problem or not.
Any ideas on how to solve the problem?
JDJoe Delphi wrote:
> Hi,
> I am BCPing in a tab-delimited text file and I am getting this
> error message:
> Starting copy...
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value
> for cast specification
> The text file records look like below:
> 200509 67195 M12AB00 67195 SSL TEST RECORD 7217 240.41
> 200509 67338 DNMXAED 67338 CTA TEST RECORD 50 237.5
> Not sure if that floating point number at the end of the file is the
> cause of this problem or not.
> Any ideas on how to solve the problem?
> JD
Have you verified there are actually TAB characters in the file where
they should be? You might try using the DTS Import Wizard to see if that
works. The wizard will show you the parsed data based on your
delimiters, so that might clue you in to the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Joe Delphi (delphi561@.nospam.cox.net) writes:
> I am BCPing in a tab-delimited text file and I am getting this error
> message:
> Starting copy...
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast specification
> The text file records look like below:
> 200509 67195 M12AB00 67195 SSL TEST RECORD 7217 240.41
> 200509 67338 DNMXAED 67338 CTA TEST RECORD 50 237.5
> Not sure if that floating point number at the end of the file is the cause
> of this problem or not.
> Any ideas on how to solve the problem?
Please post the CREATE TABLE statement for the table. It's impossible to
tell without that information what is going on.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Floating point fun

I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
migration. I've read certain floating-point values can't be represented
accurately, however, .1 doesn't look like one of them as the FLOAT(2) field
copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1 round
to .10000000000000001? Converting the column to DECIMAL is probably not an
option.
Thanks
Damien
CREATE TABLE #float_test ( ft_id INT PRIMARY KEY, rate1 FLOAT, rate2
FLOAT(2) )
-- Try and insert the value 4.1 into the float table
INSERT INTO #float_test ( ft_id, rate1 )
SELECT 1, 4.1 UNION
SELECT 2, 4 + .1 UNION
SELECT 3, '4.10' UNION
SELECT 4, 4.11 UNION
SELECT 5, 4.95 UNION
SELECT 6, CONVERT( REAL, 4.1, 0 ) UNION
SELECT 7, .1
GO
UPDATE #float_test
SET rate2 = rate1
SELECT * FROM #float_test
DROP TABLE #float_testHi
Read http://www.aspfaq.com/show.asp?id=2477
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:C4AF11D4-0AD6-4EE3-8453-FFF203829E25@.microsoft.com...
> I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
> migration. I've read certain floating-point values can't be represented
> accurately, however, .1 doesn't look like one of them as the FLOAT(2)
> field
> copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1
> round
> to .10000000000000001? Converting the column to DECIMAL is probably not
> an
> option.
> Thanks
> Damien
> CREATE TABLE #float_test ( ft_id INT PRIMARY KEY, rate1 FLOAT, rate2
> FLOAT(2) )
> -- Try and insert the value 4.1 into the float table
> INSERT INTO #float_test ( ft_id, rate1 )
> SELECT 1, 4.1 UNION
> SELECT 2, 4 + .1 UNION
> SELECT 3, '4.10' UNION
> SELECT 4, 4.11 UNION
> SELECT 5, 4.95 UNION
> SELECT 6, CONVERT( REAL, 4.1, 0 ) UNION
> SELECT 7, .1
> GO
> UPDATE #float_test
> SET rate2 = rate1
> SELECT * FROM #float_test
> DROP TABLE #float_test
>|||On Tue, 23 Aug 2005 01:43:07 -0700, Damien
<Damien@.discussions.microsoft.com> wrote:

>I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
>migration. I've read certain floating-point values can't be represented
>accurately, however, .1 doesn't look like one of them as the FLOAT(2) field
>copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1 round
>to .10000000000000001? Converting the column to DECIMAL is probably not an
>option.
It's not clear to me what you expect to happen. Floating-point values
*are* precisely represented by floating-point values. (cough)
OTOH, certain fractional values are not in the domain of certain
floating-point data types. In those cases, software generally picks
the closest value available. (The result is "error of approximation",
not a rounding error.)
You seem to think that every number that ends in '.1' should have the
same behavior. That's simply not true of floating-point data types.
It might help to think of it this way. Neighboring, distinct values in
exact data types are the same distance apart on a number line. That
is, each value in a SQL INTEGER data type is plus or minus 1 from its
neighbor.
But neighboring, distinct values in a floating-point data type are not
the same distance apart on a number line. The closer you get to zero
(from either direction), the more closely spaced the neighboring
distinct values are.
Mike Sherrill|||> Also, why does 4.1 round to .0999999999999996 but 0.1 round
to .10000000000000001?
because under the hood floats are stored as binaries. So, binary
numbers are represented accurately, up to some accuracy, of course.
Decimals are rounded to binaries. When you convert binaries back to
decimals, expect some mismatch|||On 24 Aug 2005 14:36:10 -0700, ford_desperado@.yahoo.com wrote:
[snip]
>Decimals are rounded to binaries.
Error of approximation, which seems to be what you're stumbling
toward, doesn't mean "fixed point numbers are rounded to binary". See
Knuth, vol 2.

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

floating point exception - unexplainable - even after SP4 still ge

I am in the process of translating Access 97 Databases into SQL, and working
through many very complex interrelated queries which work fine in Access. On
translating many queries all work fine apart from when I get to the top leve
l
query which effectively nests many level of queries. On trying to display
this top level view I get the horrible
Server: Msg 3628, Level 16, State 1, Line 1
A floating point exception occurred in the user process. Current transaction
is canceled.
Now I have tried to narrow the problem down, even removed any floating point
datatypes from the view but I still get the problem.
I now have a view which works if I join 3 tables but on trying to join 4
tables gives the exception. It does not matter which table I miss out...
Am I hitting some limit of SQL which does not exist in Access 97? Or is
these a nasty bug floating around which cannot handle nested views of a
certain level...
Could some clever person please could come back to this posting urgently
with when there will be another fix for this problem, or email me at
getalifestyle@.easyget.bizHi
The fix as described in
http://support.microsoft.com/defaul...kb;en-us;892840 is not
included in SP4, so you need to get a seperate hotfix for it from Microsoft.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Steve Giergiel" wrote:

> I am in the process of translating Access 97 Databases into SQL, and worki
ng
> through many very complex interrelated queries which work fine in Access.
On
> translating many queries all work fine apart from when I get to the top le
vel
> query which effectively nests many level of queries. On trying to display
> this top level view I get the horrible
> Server: Msg 3628, Level 16, State 1, Line 1
> A floating point exception occurred in the user process. Current transacti
on
> is canceled.
> Now I have tried to narrow the problem down, even removed any floating poi
nt
> datatypes from the view but I still get the problem.
> I now have a view which works if I join 3 tables but on trying to join 4
> tables gives the exception. It does not matter which table I miss out...
> Am I hitting some limit of SQL which does not exist in Access 97? Or is
> these a nasty bug floating around which cannot handle nested views of a
> certain level...
> Could some clever person please could come back to this posting urgently
> with when there will be another fix for this problem, or email me at
> getalifestyle@.easyget.biz
>|||Hi
Can you please post the SQL Script here, so that we can try to give u a
solution
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***

Floating point exception

Hi guys,
what is the problem? how can I fix it?
DBCC INDEXDEFRAG (0, table1, 2)
Server: Msg 3628, Level 16, State 1, Line 1
A floating point exception occurred in the user process. Current transaction
is canceled.With all the problems you've been having, my suggestion would be to create
your database over again from scratch.
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23S7$KOjjFHA.2444@.tk2msftngp13.phx.gbl...
> Hi guys,
> what is the problem? how can I fix it?
>
> DBCC INDEXDEFRAG (0, table1, 2)
>
> Server: Msg 3628, Level 16, State 1, Line 1
> A floating point exception occurred in the user process. Current
> transaction
> is canceled.
>|||Britney wrote:
> Hi guys,
> what is the problem? how can I fix it?
>
> DBCC INDEXDEFRAG (0, table1, 2)
>
> Server: Msg 3628, Level 16, State 1, Line 1
> A floating point exception occurred in the user process. Current
> transaction is canceled.
What version of SQL Server are you using?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||..818
I think it's because corrupted data.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eKHiWTjjFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Britney wrote:
> What version of SQL Server are you using?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Floating Point Error - Order By Mystery

I'm having a problem that I think is due to corrupt data. Depending on

the column I use in my order by clause two problems are occuring.

1. No results are returned and I get this error:
A floating point exception occured in the user process.

2. Results are returned but there are a different number of rows depending on which columns I use in my Order By clause.

Examples
SELECT * FROM SymbolStats
ORDER BY calc_date, symbol

Returns - 12207 rows but only includes one of the 25 dates in the table.

-

SELECT * from SymbolStats
ORDER BY current_hv

Returns - 0 rows.

-

SELECT * from SymbolStats
ORDER BY average_hv

Returns - floating point error

With more conditions in the WHERE clause the number of results returned varies greatly.

The

fact that different numbers of rows can be returned from the same query

only differing in how they are ordered seems like a bug.

Does this sound like corrupt data? If so, what are the best methods for fixing it?

Thanks,
patrickYou can run DBCC CHECKDB on your database to look for any possible corruption. You should also check your NT Eventlog for any hardware failures or driver issues or OS related problems.

floating point calculation

I'm trying to perform a calculation on a field in SQL Server that
should return a value with a decimal point. My problem is that the
value returned is truncated without the decimal point. Is there a
setting that needs to be turned on in SQL server to allow this?

for example

Select 20/3

should return 6.6666667

but instead I get 6"Never" <nevermind@.iname.com> wrote in message
news:e43b4225.0405071534.9a29f0a@.posting.google.co m...
> I'm trying to perform a calculation on a field in SQL Server that
> should return a value with a decimal point. My problem is that the
> value returned is truncated without the decimal point. Is there a
> setting that needs to be turned on in SQL server to allow this?
> for example
> Select 20/3
> should return 6.6666667

No, you should get 6.

Try select 20.0/3.0

You'll get 6.666666

When you say select 20/3 you're telling SQL Server you're starting with
ints, so it converts the answer to an int.

> but instead I get 6

floating point

Hi
Enybody had this error?
When I try connect to database shows weird error:
floating point exeption
MSDE working properly on other database.
Best regards
> When I try connect to database
Connect with what application? Using what mode of authentication? With
what type of user (peon, god, etc)? Anything different about this database
compared to the other database (different collation, recent restore, etc)?

> shows weird error:
> floating point exeption
Is that the whole and exact error?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
sql

float point error

We are still seeing this error:
http://support.microsoft.com/default...b;en-us;818899
on the latest version of sqlserver2k :
select @.@.version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
Does anyone know when MS will release an official patch. Suposedly, the
have a dll then can give you but nothing official. This is kindof a pretty
bad bug to leave open.
> We are still seeing this error:
> http://support.microsoft.com/default...b;en-us;818899
Well, did you follow the instructions in the article, by contacting
Microsoft product support and obtaining the .807 hotfix (which is later than
the "latest version" of .760)? There is no fee for the call or the hotfix
itself if you demonstrate to them that you are affected by the issue the
hotfix fixes.
Otherwise, you can try to find later patches (e.g. see
http://www.microsoft.com/technet/sec.../ms03-031.mspx which
updates you to .818).
Barring those two actions, you will have to wait for SP4.

> This is kindof a pretty bad bug to leave open.
Well, that really depends on how many users it has affected, doesn't it?
http://www.aspfaq.com/
(Reverse address to reply.)
|||As you know, SQL Server QFEs are cumulative. The fix described in this
article is Build 2000.00.0807. The latest publicly available hot fix is
Build .0878, which would include a fix for this error.
http://support.microsoft.com/?kbid=838166
Also know that SP4 is currently in Beta testing and will include builds up
to .0972.
Sincerely,
Anthony Thomas

"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OrJl5DOAFHA.824@.TK2MSFTNGP11.phx.gbl...
> We are still seeing this error:
> http://support.microsoft.com/default...b;en-us;818899
Well, did you follow the instructions in the article, by contacting
Microsoft product support and obtaining the .807 hotfix (which is later than
the "latest version" of .760)? There is no fee for the call or the hotfix
itself if you demonstrate to them that you are affected by the issue the
hotfix fixes.
Otherwise, you can try to find later patches (e.g. see
http://www.microsoft.com/technet/sec.../ms03-031.mspx which
updates you to .818).
Barring those two actions, you will have to wait for SP4.

> This is kindof a pretty bad bug to leave open.
Well, that really depends on how many users it has affected, doesn't it?
http://www.aspfaq.com/
(Reverse address to reply.)
|||> As you know, SQL Server QFEs are cumulative.
That is true until .977. For example, to install .993 on .760, you first
need to get to the .977 hotfix installer -- .993 won't install on < .977 (it
will complain about missing prerequisites).

> The fix described in this
> article is Build 2000.00.0807. The latest publicly available hot fix is
> Build .0878, which would include a fix for this error.
However, it is not always the best to just install the latest. He may
prefer to go through PSS, demonstrate that .807 is his issue, and fix that.
..878 might bring about other problems that he doesn't have time to fully
test. Sure, it might be more convenient to get a patch that is publicly
available, but I don't jump to the conclusion that it is absolutely the best
answer.

> Also know that SP4 is currently in Beta testing and will include builds up
> to .0972.
And then the numbering scheme jumps significantly (SP4 beta is .2026, yet
there are several .973+ hotfixes already available).
I'm still curious why they chose to break at .977 to use the new hotfix
installer, breaking the chain of cumulative hotfixes. It would have been a
much more logical break, IMHO, to wait for SP4 -- introduce the new hotfix
installer at a stable, fully tested service pack... <shrug>
A
|||Yes, I found this out with .0859. The biggest reason I jumped on .0878, it
seemed more stable.
I also noticed the large build increase on deploying the SP4 Beta (2026?)
However, from the fix list off of the beta description, it only includes
fixes through .0972 and I've seen KB listing all the way up to .1000 +. So,
I'm not sure how a Build including only fixes to the 972 level could be
labeled 2026. That's got me stumped. Not to mention that it bombs the
replication if you've already applied 878. I've got that thread running in
the Beta newsgroup.
Now, I wouldn't want to slam PSS, because they've been very helpful,
especially in crash circumstances; however, they have been known to push hot
fixes a little too eagerly as well. I figure once it has gone public, at
least, it has somewhat stabilized. But yes, I realize, that even SP level
code bases can introduce new bugs.
As always, thanks for your insight.
Sincerely,
Anthony Thomas

"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23zfCiKbAFHA.3940@.TK2MSFTNGP09.phx.gbl...
> As you know, SQL Server QFEs are cumulative.
That is true until .977. For example, to install .993 on .760, you first
need to get to the .977 hotfix installer -- .993 won't install on < .977 (it
will complain about missing prerequisites).

> The fix described in this
> article is Build 2000.00.0807. The latest publicly available hot fix is
> Build .0878, which would include a fix for this error.
However, it is not always the best to just install the latest. He may
prefer to go through PSS, demonstrate that .807 is his issue, and fix that.
..878 might bring about other problems that he doesn't have time to fully
test. Sure, it might be more convenient to get a patch that is publicly
available, but I don't jump to the conclusion that it is absolutely the best
answer.

> Also know that SP4 is currently in Beta testing and will include builds up
> to .0972.
And then the numbering scheme jumps significantly (SP4 beta is .2026, yet
there are several .973+ hotfixes already available).
I'm still curious why they chose to break at .977 to use the new hotfix
installer, breaking the chain of cumulative hotfixes. It would have been a
much more logical break, IMHO, to wait for SP4 -- introduce the new hotfix
installer at a stable, fully tested service pack... <shrug>
A
|||> Yes, I found this out with .0859. The biggest reason I jumped on .0878,
it
> seemed more stable.
I've heard many similar complaints.

> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
I'm not sure how they're going to deal with this (other than slip the other
fixes in before release). BTW, can you share any articles that are 1000+?
The highest I can find is .993 (and not using the pitiful on-again off-again
search at support.microsoft.com).
|||> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
So,
> I'm not sure how a Build including only fixes to the 972 level could be
> labeled 2026. That's got me stumped.
My guess is that the hotfixes that have been pushed since December 9th (when
2026 was forged) have been worked into both branches, and will be included
in SP4 when it goes live... what that means for the beta process, I'm not
sure... clearly we can't be testing everything right up to the point of
release, and we're already using a build that's behind on at least 6 unique
hotfixes so far.
A
|||Yea, I agree; they have to cut the beta release off at some point. I've
asked about the build number with no response though. I think it was a goof
but until I hear otherwise, we'll have to take it as they put it.
Anthony Thomas

"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23c8UxPcAFHA.3264@.TK2MSFTNGP12.phx.gbl...
> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
So,
> I'm not sure how a Build including only fixes to the 972 level could be
> labeled 2026. That's got me stumped.
My guess is that the hotfixes that have been pushed since December 9th (when
2026 was forged) have been worked into both branches, and will be included
in SP4 when it goes live... what that means for the beta process, I'm not
sure... clearly we can't be testing everything right up to the point of
release, and we're already using a build that's behind on at least 6 unique
hotfixes so far.
A
|||Yea, that's why I said .1000 +, I know I came across one article that listed
a .1193 or something like that and have been searching ever since but no
luck...yet.
I also came across a slip-steamed install for MSDE for the new, free MS ADS
(?) server. It's an SUS system for deploying patches to Server Systems. It
had a build of 8.00.0880 but can't find a build description or why it was
inserted into this installation. MS also slip-streamed SP3a, of course, and
MS03-031, individually, like 880 wouldn't install without it.
Anthony Thomas

"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uCtTUMcAFHA.3088@.TK2MSFTNGP10.phx.gbl...
> Yes, I found this out with .0859. The biggest reason I jumped on .0878,
it
> seemed more stable.
I've heard many similar complaints.

> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
I'm not sure how they're going to deal with this (other than slip the other
fixes in before release). BTW, can you share any articles that are 1000+?
The highest I can find is .993 (and not using the pitiful on-again off-again
search at support.microsoft.com).

float point error

We are still seeing this error:
http://support.microsoft.com/defaul...kb;en-us;818899
on the latest version of sqlserver2k :
select @.@.version
--
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
Does anyone know when MS will release an official patch. Suposedly, the
have a dll then can give you but nothing official. This is kindof a pretty
bad bug to leave open.> We are still seeing this error:
> http://support.microsoft.com/defaul...kb;en-us;818899
Well, did you follow the instructions in the article, by contacting
Microsoft product support and obtaining the .807 hotfix (which is later than
the "latest version" of .760)? There is no fee for the call or the hotfix
itself if you demonstrate to them that you are affected by the issue the
hotfix fixes.
Otherwise, you can try to find later patches (e.g. see
http://www.microsoft.com/technet/se...n/ms03-031.mspx which
updates you to .818).
Barring those two actions, you will have to wait for SP4.

> This is kindof a pretty bad bug to leave open.
Well, that really depends on how many users it has affected, doesn't it?
http://www.aspfaq.com/
(Reverse address to reply.)|||As you know, SQL Server QFEs are cumulative. The fix described in this
article is Build 2000.00.0807. The latest publicly available hot fix is
Build .0878, which would include a fix for this error.
http://support.microsoft.com/?kbid=838166
Also know that SP4 is currently in Beta testing and will include builds up
to .0972.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OrJl5DOAFHA.824@.TK2MSFTNGP11.phx.gbl...
> We are still seeing this error:
> http://support.microsoft.com/defaul...kb;en-us;818899
Well, did you follow the instructions in the article, by contacting
Microsoft product support and obtaining the .807 hotfix (which is later than
the "latest version" of .760)? There is no fee for the call or the hotfix
itself if you demonstrate to them that you are affected by the issue the
hotfix fixes.
Otherwise, you can try to find later patches (e.g. see
http://www.microsoft.com/technet/se...n/ms03-031.mspx which
updates you to .818).
Barring those two actions, you will have to wait for SP4.

> This is kindof a pretty bad bug to leave open.
Well, that really depends on how many users it has affected, doesn't it?
http://www.aspfaq.com/
(Reverse address to reply.)|||> As you know, SQL Server QFEs are cumulative.
That is true until .977. For example, to install .993 on .760, you first
need to get to the .977 hotfix installer -- .993 won't install on < .977 (it
will complain about missing prerequisites).

> The fix described in this
> article is Build 2000.00.0807. The latest publicly available hot fix is
> Build .0878, which would include a fix for this error.
However, it is not always the best to just install the latest. He may
prefer to go through PSS, demonstrate that .807 is his issue, and fix that.
.878 might bring about other problems that he doesn't have time to fully
test. Sure, it might be more convenient to get a patch that is publicly
available, but I don't jump to the conclusion that it is absolutely the best
answer.

> Also know that SP4 is currently in Beta testing and will include builds up
> to .0972.
And then the numbering scheme jumps significantly (SP4 beta is .2026, yet
there are several .973+ hotfixes already available).
I'm still curious why they chose to break at .977 to use the new hotfix
installer, breaking the chain of cumulative hotfixes. It would have been a
much more logical break, IMHO, to wait for SP4 -- introduce the new hotfix
installer at a stable, fully tested service pack... <shrug>
A|||Yes, I found this out with .0859. The biggest reason I jumped on .0878, it
seemed more stable.
I also noticed the large build increase on deploying the SP4 Beta (2026?)
However, from the fix list off of the beta description, it only includes
fixes through .0972 and I've seen KB listing all the way up to .1000 +. So,
I'm not sure how a Build including only fixes to the 972 level could be
labeled 2026. That's got me stumped. Not to mention that it bombs the
replication if you've already applied 878. I've got that thread running in
the Beta newsgroup.
Now, I wouldn't want to slam PSS, because they've been very helpful,
especially in crash circumstances; however, they have been known to push hot
fixes a little too eagerly as well. I figure once it has gone public, at
least, it has somewhat stabilized. But yes, I realize, that even SP level
code bases can introduce new bugs.
As always, thanks for your insight.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23zfCiKbAFHA.3940@.TK2MSFTNGP09.phx.gbl...
> As you know, SQL Server QFEs are cumulative.
That is true until .977. For example, to install .993 on .760, you first
need to get to the .977 hotfix installer -- .993 won't install on < .977 (it
will complain about missing prerequisites).

> The fix described in this
> article is Build 2000.00.0807. The latest publicly available hot fix is
> Build .0878, which would include a fix for this error.
However, it is not always the best to just install the latest. He may
prefer to go through PSS, demonstrate that .807 is his issue, and fix that.
.878 might bring about other problems that he doesn't have time to fully
test. Sure, it might be more convenient to get a patch that is publicly
available, but I don't jump to the conclusion that it is absolutely the best
answer.

> Also know that SP4 is currently in Beta testing and will include builds up
> to .0972.
And then the numbering scheme jumps significantly (SP4 beta is .2026, yet
there are several .973+ hotfixes already available).
I'm still curious why they chose to break at .977 to use the new hotfix
installer, breaking the chain of cumulative hotfixes. It would have been a
much more logical break, IMHO, to wait for SP4 -- introduce the new hotfix
installer at a stable, fully tested service pack... <shrug>
A|||> Yes, I found this out with .0859. The biggest reason I jumped on .0878,
it
> seemed more stable.
I've heard many similar complaints.

> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
I'm not sure how they're going to deal with this (other than slip the other
fixes in before release). BTW, can you share any articles that are 1000+?
The highest I can find is .993 (and not using the pitiful on-again off-again
search at support.microsoft.com).|||> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
So,
> I'm not sure how a Build including only fixes to the 972 level could be
> labeled 2026. That's got me stumped.
My guess is that the hotfixes that have been pushed since December 9th (when
2026 was forged) have been worked into both branches, and will be included
in SP4 when it goes live... what that means for the beta process, I'm not
sure... clearly we can't be testing everything right up to the point of
release, and we're already using a build that's behind on at least 6 unique
hotfixes so far.
A|||Yea, I agree; they have to cut the beta release off at some point. I've
asked about the build number with no response though. I think it was a goof
but until I hear otherwise, we'll have to take it as they put it.
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23c8UxPcAFHA.3264@.TK2MSFTNGP12.phx.gbl...
> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
So,
> I'm not sure how a Build including only fixes to the 972 level could be
> labeled 2026. That's got me stumped.
My guess is that the hotfixes that have been pushed since December 9th (when
2026 was forged) have been worked into both branches, and will be included
in SP4 when it goes live... what that means for the beta process, I'm not
sure... clearly we can't be testing everything right up to the point of
release, and we're already using a build that's behind on at least 6 unique
hotfixes so far.
A|||Yea, that's why I said .1000 +, I know I came across one article that listed
a .1193 or something like that and have been searching ever since but no
luck...yet.
I also came across a slip-steamed install for MSDE for the new, free MS ADS
(?) server. It's an SUS system for deploying patches to Server Systems. It
had a build of 8.00.0880 but can't find a build description or why it was
inserted into this installation. MS also slip-streamed SP3a, of course, and
MS03-031, individually, like 880 wouldn't install without it.
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uCtTUMcAFHA.3088@.TK2MSFTNGP10.phx.gbl...
> Yes, I found this out with .0859. The biggest reason I jumped on .0878,
it
> seemed more stable.
I've heard many similar complaints.

> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
I'm not sure how they're going to deal with this (other than slip the other
fixes in before release). BTW, can you share any articles that are 1000+?
The highest I can find is .993 (and not using the pitiful on-again off-again
search at support.microsoft.com).

float point error

We are still seeing this error:
http://support.microsoft.com/default.aspx?scid=kb;en-us;818899
on the latest version of sqlserver2k :
select @.@.version
--
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
Does anyone know when MS will release an official patch. Suposedly, the
have a dll then can give you but nothing official. This is kindof a pretty
bad bug to leave open.> We are still seeing this error:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;818899
Well, did you follow the instructions in the article, by contacting
Microsoft product support and obtaining the .807 hotfix (which is later than
the "latest version" of .760)? There is no fee for the call or the hotfix
itself if you demonstrate to them that you are affected by the issue the
hotfix fixes.
Otherwise, you can try to find later patches (e.g. see
http://www.microsoft.com/technet/security/bulletin/ms03-031.mspx which
updates you to .818).
Barring those two actions, you will have to wait for SP4.
> This is kindof a pretty bad bug to leave open.
Well, that really depends on how many users it has affected, doesn't it?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||As you know, SQL Server QFEs are cumulative. The fix described in this
article is Build 2000.00.0807. The latest publicly available hot fix is
Build .0878, which would include a fix for this error.
http://support.microsoft.com/?kbid=838166
Also know that SP4 is currently in Beta testing and will include builds up
to .0972.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OrJl5DOAFHA.824@.TK2MSFTNGP11.phx.gbl...
> We are still seeing this error:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;818899
Well, did you follow the instructions in the article, by contacting
Microsoft product support and obtaining the .807 hotfix (which is later than
the "latest version" of .760)? There is no fee for the call or the hotfix
itself if you demonstrate to them that you are affected by the issue the
hotfix fixes.
Otherwise, you can try to find later patches (e.g. see
http://www.microsoft.com/technet/security/bulletin/ms03-031.mspx which
updates you to .818).
Barring those two actions, you will have to wait for SP4.
> This is kindof a pretty bad bug to leave open.
Well, that really depends on how many users it has affected, doesn't it?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||> As you know, SQL Server QFEs are cumulative.
That is true until .977. For example, to install .993 on .760, you first
need to get to the .977 hotfix installer -- .993 won't install on < .977 (it
will complain about missing prerequisites).
> The fix described in this
> article is Build 2000.00.0807. The latest publicly available hot fix is
> Build .0878, which would include a fix for this error.
However, it is not always the best to just install the latest. He may
prefer to go through PSS, demonstrate that .807 is his issue, and fix that.
.878 might bring about other problems that he doesn't have time to fully
test. Sure, it might be more convenient to get a patch that is publicly
available, but I don't jump to the conclusion that it is absolutely the best
answer.
> Also know that SP4 is currently in Beta testing and will include builds up
> to .0972.
And then the numbering scheme jumps significantly (SP4 beta is .2026, yet
there are several .973+ hotfixes already available).
I'm still curious why they chose to break at .977 to use the new hotfix
installer, breaking the chain of cumulative hotfixes. It would have been a
much more logical break, IMHO, to wait for SP4 -- introduce the new hotfix
installer at a stable, fully tested service pack... <shrug>
A|||Yes, I found this out with .0859. The biggest reason I jumped on .0878, it
seemed more stable.
I also noticed the large build increase on deploying the SP4 Beta (2026?)
However, from the fix list off of the beta description, it only includes
fixes through .0972 and I've seen KB listing all the way up to .1000 +. So,
I'm not sure how a Build including only fixes to the 972 level could be
labeled 2026. That's got me stumped. Not to mention that it bombs the
replication if you've already applied 878. I've got that thread running in
the Beta newsgroup.
Now, I wouldn't want to slam PSS, because they've been very helpful,
especially in crash circumstances; however, they have been known to push hot
fixes a little too eagerly as well. I figure once it has gone public, at
least, it has somewhat stabilized. But yes, I realize, that even SP level
code bases can introduce new bugs.
As always, thanks for your insight.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23zfCiKbAFHA.3940@.TK2MSFTNGP09.phx.gbl...
> As you know, SQL Server QFEs are cumulative.
That is true until .977. For example, to install .993 on .760, you first
need to get to the .977 hotfix installer -- .993 won't install on < .977 (it
will complain about missing prerequisites).
> The fix described in this
> article is Build 2000.00.0807. The latest publicly available hot fix is
> Build .0878, which would include a fix for this error.
However, it is not always the best to just install the latest. He may
prefer to go through PSS, demonstrate that .807 is his issue, and fix that.
.878 might bring about other problems that he doesn't have time to fully
test. Sure, it might be more convenient to get a patch that is publicly
available, but I don't jump to the conclusion that it is absolutely the best
answer.
> Also know that SP4 is currently in Beta testing and will include builds up
> to .0972.
And then the numbering scheme jumps significantly (SP4 beta is .2026, yet
there are several .973+ hotfixes already available).
I'm still curious why they chose to break at .977 to use the new hotfix
installer, breaking the chain of cumulative hotfixes. It would have been a
much more logical break, IMHO, to wait for SP4 -- introduce the new hotfix
installer at a stable, fully tested service pack... <shrug>
A|||> Yes, I found this out with .0859. The biggest reason I jumped on .0878,
it
> seemed more stable.
I've heard many similar complaints.
> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
I'm not sure how they're going to deal with this (other than slip the other
fixes in before release). BTW, can you share any articles that are 1000+?
The highest I can find is .993 (and not using the pitiful on-again off-again
search at support.microsoft.com).|||> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
So,
> I'm not sure how a Build including only fixes to the 972 level could be
> labeled 2026. That's got me stumped.
My guess is that the hotfixes that have been pushed since December 9th (when
2026 was forged) have been worked into both branches, and will be included
in SP4 when it goes live... what that means for the beta process, I'm not
sure... clearly we can't be testing everything right up to the point of
release, and we're already using a build that's behind on at least 6 unique
hotfixes so far.
A|||Yea, I agree; they have to cut the beta release off at some point. I've
asked about the build number with no response though. I think it was a goof
but until I hear otherwise, we'll have to take it as they put it.
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23c8UxPcAFHA.3264@.TK2MSFTNGP12.phx.gbl...
> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
So,
> I'm not sure how a Build including only fixes to the 972 level could be
> labeled 2026. That's got me stumped.
My guess is that the hotfixes that have been pushed since December 9th (when
2026 was forged) have been worked into both branches, and will be included
in SP4 when it goes live... what that means for the beta process, I'm not
sure... clearly we can't be testing everything right up to the point of
release, and we're already using a build that's behind on at least 6 unique
hotfixes so far.
A|||Yea, that's why I said .1000 +, I know I came across one article that listed
a .1193 or something like that and have been searching ever since but no
luck...yet.
I also came across a slip-steamed install for MSDE for the new, free MS ADS
(?) server. It's an SUS system for deploying patches to Server Systems. It
had a build of 8.00.0880 but can't find a build description or why it was
inserted into this installation. MS also slip-streamed SP3a, of course, and
MS03-031, individually, like 880 wouldn't install without it.
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uCtTUMcAFHA.3088@.TK2MSFTNGP10.phx.gbl...
> Yes, I found this out with .0859. The biggest reason I jumped on .0878,
it
> seemed more stable.
I've heard many similar complaints.
> I also noticed the large build increase on deploying the SP4 Beta (2026?)
> However, from the fix list off of the beta description, it only includes
> fixes through .0972 and I've seen KB listing all the way up to .1000 +.
I'm not sure how they're going to deal with this (other than slip the other
fixes in before release). BTW, can you share any articles that are 1000+?
The highest I can find is .993 (and not using the pitiful on-again off-again
search at support.microsoft.com).

fixed point arithmetic for price calculations

I've got a price in euro as a string, which I can easily cast to a numeric SSIS data type e.g. R4, R8, DECIMAL, NUMERIC. And I've got the dollar/euro exchange rate stored in an SSIS variable of type DOUBLE, set to 1.28 for testing purposes. I want to multiply the two values and return the (dollar) result, rounded (not truncated) to 2 decimal places, as a string.

Here are some experiments I did in an SSIS expression editor:

(DT_WSTR, 10) (1.28 * 31.10) evaluates to "39.8080"

(DT_WSTR, 10) (1.28 * (DT_R8) "31.10") evaluates to "39.808"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 0) "31.10") evaluates to "39.68"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 1) "31.10") evaluates to "39.808"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 2) "31.10") evaluates to "39.8080"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 3) "31.10") evaluates to "39.80800"

Of course, what I really want is "39.81", so I went on:

(DT_WSTR, 10) ((DT_DECIMAL, 0) (1.28 * (DT_R8) "31.10")) evaluates to "39"

(DT_WSTR, 10) ((DT_DECIMAL, 1) (1.28 * (DT_R8) "31.10")) evaluates to "39.8"

This looks promising! But:

(DT_WSTR, 10) ((DT_DECIMAL, 2) (1.28 * (DT_R8) "31.10")) evaluates to "39.8"

(DT_WSTR, 10) ((DT_DECIMAL, 3) (1.28 * (DT_R8) "31.10")) evaluates to "39.808"

Argh... How does one get a floating point value rounded to 2 decimal places?

(DT_NUMERIC, 6,2)(1.28 * 31.10) ?|||

Phil Brammer wrote:

(DT_NUMERIC, 6,2)(1.28 * 31.10) ?

Hmm... That didn't seem to work either.|||Use the ROUND() function. That will work for you.

ROUND(1.28 * 31.1 ,2)|||

(DT_WSTR, 10) ROUND(1.28 * 31.10, 2) indeed evaluates to "39.8100", which can simply be truncated.

I didn't think to try ROUND(numeric_expression, length) because the help text in the Expression Builder says that it returns an integer (regardless of the length parameter).

Thanks!

|||

Kevin Rodgers wrote:

(DT_WSTR, 10) ROUND(1.28 * 31.10, 2) indeed evaluates to "39.8100", which can simply be truncated.

Hmmm. I had to increase the string length from 10 to avoid a truncation error, so I changed it to 4000 which I think is the maximum for Unicode strings -- no worries. But more testing reveals that (DT_WSTR, 4000) ROUND(numeric_expression, 2) sometimes returns a value with fewer than 2 digits after the decimal point e.g. "16" instead of "16.00" or "133.5" insead of "133.50"'.

Here's what I'm using to ensure that there is a decimal point followed by 2 digits in the result:

FINDSTRING(usd_price, ".", 1) > 0 ? SUBSTRING(usd_price + "00", 1, FINDSTRING(usd_price, ".", 1) + 2) : usd_price + ".00"