We just set up a new SQL Server 2008 database, and I got the link set up between our server and an iSeries As400 server that we get some of our data from.

The problem I'm having is that it is rounding down any floating numbers, which is a pain because we're working with dollar amounts a lot of the time.

select * FROM openquery(NADS, 'select CMD, 1.23 Type from CREDD')

On our older server this returns the CMD column, and a column called Type with 1.23 all the way down. On the new server type is just 1.00

So far as I can tell, the link part is set up the same. The IBM i Access for Windows ODBC Setup is mostly the same, but there are some differences between versions that I'm not sure about.

Edit: In case it matters, For the provider I'm using Microsoft OLE DB Provider for ODBC Drivers


provider options are the same
provider options are the same

Linked Server properties: enter image description here

iSeries Access for windows ODBC setup appears to be the same enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here

  • What is the data type on each side?
    – Mike Walsh
    Nov 13 '13 at 20:44
  • I'm not sure what it is on the iSeries side. I'm assuming some kind of float. I'm just doing a select * on my side, so unless openquery is converting the data it should be the same...
    – AndyD273
    Nov 13 '13 at 20:50
  • It is rare to see Float used in an IBM i database. We generally use fixed point (packed) Decimal or (zoned) Numeric. Occasionally you might see some size Int, but float is very rare. Our systems tend to be used for business applications where exact numbers are preferred.
    – WarrenT
    Nov 13 '13 at 23:05
  • You might try IBM Data Studio to look at the table definition. SYSCOLUMNS would be the catalog view to look in.
    – WarrenT
    Nov 13 '13 at 23:07
  • As you said, the new server is working differently from the old server. So what is different on your new server?
    – WarrenT
    Nov 14 '13 at 3:33

Figured it out!!

Apparently we didn't have the latest service pack installed. We had to install the 64bit version of the service pack (32bit version failed to install), and when it finished the decimal numbers were coming across.

EDIT: In case anyone was wondering:
We're using "IBM iAccess for Windows 7.1 (07.01.0800) (64bit)" and using "Microsoft OLE DB Provider for ODBC Drivers" as the linked server sql driver.

EDIT 2017: Seeing as I just ran into this problem again and had to hunt down the service pack again, here is the link to the service pack download page, so that in a few years when I run into this problem again I will find it easier...


  • Nothing like finding your own SE answer to solve an issue :) Nov 29 '17 at 19:25
  • @LowlyDBA Twice actually. Apparently it's a problem every couple years when they upgrade the server but don't put the service packs on, and then come complaining when the data is wrong. "Didn't I fix this problem before? Hold on, let me check SE..."
    – AndyD273
    Nov 29 '17 at 20:17

The solution is simple: let AS400 convert the value in float because implicit conversion applied by the iSeries provider fails almost everytime. So, very simply:

select * FROM openquery(NADS, 'select CMD, cast(1.23 as float) Type from CREDD')

In this way all works fine: 1.23 will be float in Sql Server too

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.