canuckconsulting
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:
But this statement gives an error:
The error is:
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')
But this statement gives an error:
Select UnitCost from test2.mnprod..oslines
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
canuckconsulting, did you try Gary's suggestion?