ODBC Linked Server - AS/400 data truncated in SQL Server but fine in MS Query

We have an AS/400 DB which we connect to using a 64-bit ODBC driver.  If i connect to the ODBC driver using Excel's Microsoft Query data returns fine.

If however I run the same query in SQL Server 2012 with the ODBC referenced as a linked server using the MS OLE DB Provider for ODBC Drivers the data is wrong  Specifically the decimals types have the decimal portions truncated (so 17.45 is returned as 17.00).

A possibly clue to this is I am having to run the SQL in an OPENQUERY as I receive an error regarding metadata.  As am example, the following runs fine:

Select * from OPENQUERY(test2, 'Select UnitCost from oslines')

Open in new window


But this statement gives an error:

Select UnitCost from test2.mnprod..oslines

Open in new window


The error is:

The OLE DB provider "MSDASQL" for linked server "test2" supplied inconsistent metadata for a column. The column "LineNo" (compile-time ordinal 2) of object ""mnprod"."oslines"" was reported to have a "DBTYPE" of 2 at compile time and 18 at run time.
canuckconsultingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gary PattersonVP Technology / Senior Consultant Commented:
What's the native DB2 data type on this column?

Suggest you try the using IBM i DB2 OLE DB driver instead for the link.  Eliminates an entire software layer between you and the database compared to using ODBC through the MSDASQL driver.

http://www-01.ibm.com/support/docview.wss?uid=nas8N1014514

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
canuckconsulting, did you try Gary's suggestion?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.