Link to home
Start Free TrialLog in
Avatar of canuckconsulting
canuckconsultingFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Gary Patterson, CISSP
Gary Patterson, CISSP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
canuckconsulting, did you try Gary's suggestion?