FIX: Float Data Inaccurately Converted When Sent to Linked Server

Article translations Article translations
Article ID: 265865 - View products that this article applies to.
This article was previously published under Q265865
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #:58044 (SQLBUG_70)
Expand all | Collapse all

On This Page

SYMPTOMS

When an xp_logevent data type is sent to a linked server the data type might be converted to a higher number. Comparisons between two float numbers on a linked server might render inaccurate results.

CAUSE

The Profiler trace execution plan shows that the client SQL Server server (the linking server) rounds the number to a higher value before sending the number to the back-end SQL Server server (linked server).

WORKAROUND

You can use these steps to work around this problem:
  1. Use a variable of the same data type as the remote column. For example:
        declare @p1 float
        set @p1=600393500 
        select * from Server1.pubs.dbo.table1 where c1 < @p1
    					
  2. Cast the remote column to a numeric value of appropriate precision/scale:
    select * from Server1.pubs.dbo.table1 where convert(numeric(28,8), c1) > 600393500
    					
  3. Use the OPENQUERY function to send the query to the linked server.
  4. Change the data type of the column on the linked server.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.

MORE INFORMATION

To reproduce this problem, follow these steps:
  1. On Server1 create a table with a float column and insert a float value:
    use pubs
    go
    create table table1 (c1 float)
    insert into table1 values (600393932.22354996)
    					
  2. On Server2 create a linked server to Server1:
    exec master..sp_addlinkedserver 'Server1'
    					
  3. On Server2 run the following query:
    select * from Server1.pubs.dbo.table1 where c1 < 600393500 
    					
This query returns 600393932.2235, which is greater, not less, than 600393500.

REFERENCES

For additional information on the float data type, click the article numbers below to view the articles in the Microsoft Knowledge Base:
125056 INFO: Precision and Accuracy in Floating-Point Calculations
145889 INFO: Why Floating Point Numbers May Lose Precision
36068 INFO: IEEE Floating-Point Representation and MS Languages

Properties

Article ID: 265865 - Last Review: October 20, 2013 - Revision: 2.1
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix kbqfe KB265865

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com