Loss of precision using OpenQuery

Hello,

I have created a function which returns records from a linked server using OPENQUERY (text file delimited with ';') into a TABLE variable.

CREATE FUNCTION [dbo].[fct_PremierTable_IMO]()
      RETURNS TABLE
AS
RETURN
(
      SELECT ZPRXSO, ZPRXVT, ZTVARV
      FROM OPENQUERY (IMO, 'SELECT * FROM IMMOS.txt')
)

The target Table variable is as follows:
DECLARE @tTable TABLE
(
                IDENTITE INT IDENTITY (1, 1) PRIMARY KEY NOT NULL,
          ZPRXSO DECIMAL(18,2),       ZPRXVT DECIMAL(18,2),       ZTVARV DECIMAL(18,2)
)

The problem is that the decimal part of the values are lost. For example, 2356 is obtained instead of 2356.58.
I have tried to use VARCHAR instead of DECIMAL without any success.

Please help!

Rishi
rrishiAsked:
Who is Participating?
 
g0rathConnect With a Mentor Commented:
cross platform work with decimal types can be hard, the simplest approach that works for me 100% of the time is to take the decimal type, multipy by 100 and transfer it as an integer type, then convert at my local point to decimal, money, float or whatever and just divide the set by 100.

Nothing is worse then 10.42 becoming 10.41999999 due to percision issues
0
 
ShogunWadeCommented:
what driver are you using in your linked server ?
0
 
rrishiAuthor Commented:
Thanks a lot, it seems to do the trick!
Im using OLEDB Jet 4.0 driver.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.