FIX: Float Data Inaccurately Converted When Sent to Linked Server (265865)

The information in this article applies to:

Microsoft SQL Server 7.0

This article was previously published under Q265865

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:

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

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

Use the OPENQUERY function to send the query to the linked server.
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:

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)

On Server2 create a linked server to Server1:

exec master..sp_addlinkedserver 'Server1'

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

Modification Type:	Major	Last Reviewed:	3/14/2006
Keywords:	kbBug kbfix kbQFE KB265865