One of the queries invovled selecting all columns from a table. So I had no doubt that
select A,B,C,D,AMT
from TBL
query would work. Whenever I ran this query in SQL Manage Studio I got this message:
"OLE DB provider 'MSDASQL' for linked server 'LS' returned message 'Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.' Cannot get the current row value of column LS..SCH.TBL.AMT from from OLE DB provider 'MSDASQL' for linked server 'LS' Could not convert the data value due to reasons other than sign mismatch or overflow"
By process of elmination I found out that the culprit is AMT. After some reading through Cache documentations [http://vista.intersystems.com/csp/docbook/DocBook.UI.Page.cls?KEY=GIC_INTRO ; and other places ] I solved this problem. Basically although the ODBC presented the AMT column as Currency or [decimal in the linked server] that did not mean that Cache really stored only decimal in that column. And somehow that column contained alphabet characters which caused the query to fail every time it asked for the AMT column.
And to solve this problem through SQL Server I ran the following query instead
SELECT A,B,C,D,AMT
FROM
OPENQUERY(LS,'SELECT A,B,C,D,convert(nvarchar(50),AMT) as AMT FROM TBL');
I ran into other problems with other columns and the cause is basically the discrepancy between the way the ODBC driver sees Cache columns and their datatypes and the actual layout of these columns in Cache. And openquery does a good job in helping to solve this problem.
