FIX: Float Data Inaccurately Converted When Sent to Linked Server

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)
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 pubsgocreate 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: 10/20/2013 22:13:11 - Revision: 2.1

  • Microsoft SQL Server 7.0 Standard Edition
  • kbnosurvey kbarchive kbbug kbfix kbqfe KB265865
Feedback