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 return

When the table below is created -the data selected
is different for values > 10. Does some one know why
float behaves this way ? - I am stumped
create table tempdb.dbo.TestValue (ColId int, TheValue
Float)
Insert into TestValue Values (1, 10.25)
Insert into TestValue Values (2, 10.99)
Insert into TestValue Values (3, 9.9)
Insert into TestValue Values (4, 6.59)
select * from TestValue
Results:
========
1 10.25
2 10.99
3 9.9000000000000004
4 6.5899999999999999PBrent
Read up "float and real" chapter in the BOL as well as visit on Aaron's web
site www.aspfaq.com to get more info and examples whu this datatype behaves
this way.
"PBrent" <PBrent@.discussions.microsoft.com> wrote in message
news:23d001c53f65$e8aff6a0$a401280a@.phx.gbl...
> When the table below is created -the data selected
> is different for values > 10. Does some one know why
> float behaves this way ? - I am stumped
> create table tempdb.dbo.TestValue (ColId int, TheValue
> Float)
> Insert into TestValue Values (1, 10.25)
> Insert into TestValue Values (2, 10.99)
> Insert into TestValue Values (3, 9.9)
> Insert into TestValue Values (4, 6.59)
> select * from TestValue
> Results:
> ========
> 1 10.25
> 2 10.99
> 3 9.9000000000000004
> 4 6.5899999999999999|||It's nothing special about 10. If you insert the values 16.9 into a float,
the actual floating-point value stored is just under 16.9, and you get this
Insert into TestValue Values (5, 16.9)
...
16.899999999999999
Of the numbers you inserted into the table, only 10.25 can be
stored exactly as a float. The others are stored as the nearest
representable floating-point value. When these approximations
are converted back to decimals for display, sometimes you see
the difference from the original number you tried to enter, and
sometimes you are lucky and they are rounded back to the
number you started with.
Steve Kass
Drew University
PBrent wrote:

>When the table below is created -the data selected
>is different for values > 10. Does some one know why
>float behaves this way ? - I am stumped
>create table tempdb.dbo.TestValue (ColId int, TheValue
>Float)
>Insert into TestValue Values (1, 10.25)
>Insert into TestValue Values (2, 10.99)
>Insert into TestValue Values (3, 9.9)
>Insert into TestValue Values (4, 6.59)
>select * from TestValue
>Results:
>========
>1 10.25
>2 10.99
>3 9.9000000000000004
>4 6.5899999999999999
>

Flawed SQL Procedure

I am using the below procedure to set the field "Completed" to "True" in the table "Orders" only when the customer have paid and received or downloaded all his produts.

~~~~~~~~~~~~~~~~~~~~~~~~~

ALTER PROCEDURESetOrderToCompleted

(@.UserNameVARCHAR(50))

AS

UPDATEOrders

SETCompleted = 1

WHEREUserName = @.UserName

ANDCompleted = 0

~~~~~~~~~~~~~~~~~~~~~~~~~

Which is obviously flawed because I predict a situation where thesame customer ( user1 ) could havetwo different orders, like in the below example, when this procedurewill set incorrectly both fields "Completed" to "True" ( in tableOrders ) forOrderID = 1 and OrderID = 2 when actually thenot-downloadable product "gadget105" wasnot received yet by the customer (Received=False in table "OrderDetails" ).

Observations:

1)Downloadable products likesoftware have their field "Received" set toNULL because theydo not need to be shipped and therefore completing this field is irrelevant.

2) Both orders (OrderID = 1 and 2 ) were made by thesame customer withUserName = "user1".

3) The above procedure is only executed after all the downloadable products of the order have being downloaded by the customer.

Table OrderDetails

_______________________________________________________________

OrderID ProductID ProductName Downloadable Quantity Received UnitCost

1 10 software10 True 1 NULL 15.00

1 101 gadget101 False 1 True 20.00

2 12 software12 True 1 NULL 16.00

2 105 gadget105 False 1 False 22.00

2 13 software13 True 1 NULL 22.00

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Table Orders

_______________________________________________________________

OrderID UserName PaymentConfirmed Completed

1 user1 True False

2 user1 True False

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

How to solve the problem ?

You woul dprobably need to use the OrderId also in the WHERE clause.. so only the specific orders get "completed"|||

Hi ndinakar

But that is the problem, the procedure itself has to be capable to find out which OrderIDs must set the field Completed to True in the table .

|||

I can obtain the information that all downloadable items were downloaded by the client by verifing the field "RemoveRole" in theCustomerDownload table ( not shown here ) set to 'yes'.

Based on that, I devised this new procedure but since I am not good with "INNER JOINs", can somebody tell me if it is correct ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ALTER PROCEDURESetOrderToCompleted

(@.UserNameVARCHAR(50))

AS

UPDATEOrders

SETCompleted = 1

WHEREOrderID = (SELECT OrderID

FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrdeID

INNER JOIN CustomerDownload ON Orders.OrderID = CustomerDownload.OrderID

WHERE Orders.UserName = @.UserName

ANDCustomerDownload.RemoveRole='yes'

AND OrderDetails.Received = 1

AND Orders.Completed = 0)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In this procedure I aim to set the field Completed to True for all the customer's orders that satisfy the above conditions.

|||

If your subquery returs multiple records your query could fail. Perhaps you might want to do an IN instead of "=".

UPDATEOrders

SETCompleted = 1

WHEREOrderID IN (SELECT OrderID

FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrdeID

INNER JOIN CustomerDownload ON Orders.OrderID = CustomerDownload.OrderID

WHERE Orders.UserName = @.UserName

ANDCustomerDownload.RemoveRole='yes'

AND OrderDetails.Received = 1

AND Orders.Completed = 0)

Flat File Data Flow

any suggestions on dealing with a flat file in the format below. I only want to process the data columns in the middle of the file and want to ignore all other rows. This was a very simple task in DTS with a small amount of VBScript in the transformation but it doesn't seem as straightforward in SSIS. thanks

......... file example ......

start-of-file

header1

header2

...

start-of-data

col0|col1|col2|col3|....

col0|col1|col2|col3|....

col0|col1|col2|col3|....

end-of-data

end-of-file

Two steps.

Read the file in first as one big text string (for each row) and pass them through a Conditional Split transformation to filter off each row that you don't want. Then hook it to a flat file destination.

Then use another flat file source against the file just created to do your column parsing. Work with it as needed from there.

Searching this forum will also yield other options (substrings, etc...) that you can try.|||thanks phil. thats helps

Fixing my table based on Dbcc Showcontig results

Can someone please help me interpret this result set below and suggest
on way I can speed up my table? What changes should I make?

DBCC SHOWCONTIG scanning 'tblListing' table...
Table: 'tblListing' (1092914965); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned........................: 97044
- Extents Scanned.......................: 12177
- Extent Switches.......................: 13452
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 90.17% [12131:13453]
- Logical Scan Fragmentation ..............: 0.86%
- Extent Scan Fragmentation ...............: 2.68%
- Avg. Bytes Free per Page................: 1415.8
- Avg. Page Density (full)................: 82.51%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

Thank you.You should take a look at ttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. Let us know if you have any questions after reading this. Keep in mind that it's quite possible that given your server workload, index defragmentation isn't at all necessary.

Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"SR" <yosonu@.socal.rr.com> wrote in message news:ba4c21f6.0406140740.7de00c3a@.posting.google.c om...
Can someone please help me interpret this result set below and suggest
on way I can speed up my table? What changes should I make?

DBCC SHOWCONTIG scanning 'tblListing' table...
Table: 'tblListing' (1092914965); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned........................: 97044
- Extents Scanned.......................: 12177
- Extent Switches.......................: 13452
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 90.17% [12131:13453]
- Logical Scan Fragmentation ..............: 0.86%
- Extent Scan Fragmentation ...............: 2.68%
- Avg. Bytes Free per Page................: 1415.8
- Avg. Page Density (full)................: 82.51%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

Thank you.|||"SR" <yosonu@.socal.rr.com> wrote in message
news:ba4c21f6.0406140740.7de00c3a@.posting.google.c om...
> Can someone please help me interpret this result set below and suggest
> on way I can speed up my table? What changes should I make?
> DBCC SHOWCONTIG scanning 'tblListing' table...
> Table: 'tblListing' (1092914965); index ID: 1, database ID: 13
> TABLE level scan performed.
> - Pages Scanned........................: 97044
> - Extents Scanned.......................: 12177
> - Extent Switches.......................: 13452
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 90.17% [12131:13453]
> - Logical Scan Fragmentation ..............: 0.86%
> - Extent Scan Fragmentation ...............: 2.68%
> - Avg. Bytes Free per Page................: 1415.8
> - Avg. Page Density (full)................: 82.51%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Thank you.

http://www.sql-server-performance.c..._showcontig.asp

At first glance, the output seems fine - there is very little fragmentation,
and the scan density is high. If you're having performance issues with this
table, you may want to give some more information. In particular, the CREATE
TABLE and CREATE INDEX statements, plus a query which is performing badly,
and the reason why you believe this table is the problem (eg. the execution
plan for the query).

Simon

First 4 symbols of a string truncated when inserting typed XML

Dear SQL experts, is it a bug or I'm understanding something wrong?

Please look at the script below. Is it a bug? If yes, when can we expect it to be fixed and released? Are there any workarounds apart from just not using xsd:string type?

IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[TestSchema]')

DROP XML SCHEMA COLLECTION [dbo].[TestSchema]

CREATE XML SCHEMA COLLECTION [dbo].[TestSchema] AS N'

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="TEST" elementFormDefault="qualified">

<xsd:element name="Root" type="xsd:string"/>

</xsd:schema>'

GO

DECLARE @.doc xml (TestSchema)

SET @.doc = ''

-- Inserting string of 64 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234</Root>

into (/)')

-- Inserting string of 70 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234567890</Root>

into (/)')

-- Inserting string of 10 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a</Root>

into (/)')

SELECT @.doc

Here is the result:

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234</Root>

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234567890</Root>

<Root xmlns="TEST">123456789a</Root>

You can see that first two strings are truncated of 4 characters at the start.

The problem occurs in case if:

1. The variable or column is typed XML.

2. Type of the string is xsd:string or derived.

3. The string is longer than 63 characters (no matter how much longer, what is truncated are alwasy first 4 characters).

Thanks a lot in advance!

This is known issue. We have fixed this bug in Yukon SP2.

Jinghao Liu

|||

Hello Jinghao,

Thanks for the prompt answer!

And do you know approximate term - when the SP2 is going to be released to public?

Best regards,

Yuriy

|||

You can ask your SQL support contact about the schedule of Yukon Service Pack 2. They usually know better than us about the date. If you are important customer, you might get the early version of it.

|||Thanks, we'll try to.

First 4 symbols of a string truncated when inserting typed XML

Dear SQL experts, is it a bug or I'm understanding something wrong?

Please look at the script below. Is it a bug? If yes, when can we expect it to be fixed and released? Are there any workarounds apart from just not using xsd:string type?

IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[TestSchema]')

DROP XML SCHEMA COLLECTION [dbo].[TestSchema]

CREATE XML SCHEMA COLLECTION [dbo].[TestSchema] AS N'

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="TEST" elementFormDefault="qualified">

<xsd:element name="Root" type="xsd:string"/>

</xsd:schema>'

GO

DECLARE @.doc xml (TestSchema)

SET @.doc = ''

-- Inserting string of 64 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234</Root>

into (/)')

-- Inserting string of 70 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234567890</Root>

into (/)')

-- Inserting string of 10 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a</Root>

into (/)')

SELECT @.doc

Here is the result:

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234</Root>

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234567890</Root>

<Root xmlns="TEST">123456789a</Root>

You can see that first two strings are truncated of 4 characters at the start.

The problem occurs in case if:

1. The variable or column is typed XML.

2. Type of the string is xsd:string or derived.

3. The string is longer than 63 characters (no matter how much longer, what is truncated are alwasy first 4 characters).

Thanks a lot in advance!

This is known issue. We have fixed this bug in Yukon SP2.

Jinghao Liu

|||

Hello Jinghao,

Thanks for the prompt answer!

And do you know approximate term - when the SP2 is going to be released to public?

Best regards,

Yuriy

|||

You can ask your SQL support contact about the schedule of Yukon Service Pack 2. They usually know better than us about the date. If you are important customer, you might get the early version of it.

|||Thanks, we'll try to.