Re: MSSQL and AS400 Query

[Kirk Goins <kirkgoins@xxxxxxxxx>]

I am not an SQL Person but 2 things..
#1 make sure your ODBC driver is current. I have broken a lot of ODBC stiff
with down level version of iSeries Access after an OS Upgrade. So Update
IBM i Access for Windows ( Client Access ) to 7.1 with the latest Service
Pack.

#2 Read up on the changes on SQL JOIN support. At V5R4 IBM let somethings
slide with only a warning message in the logs. V6R1 broke some of those and
at V7R1 more broke because they are not to standard.




On Sun, Apr 7, 2013 at 7:10 AM, Billy Waters <bwaters@xxxxxxxxxxxxxxxxxx>wrote:

> Using ODBC Linked Server to AS400. After upgrading to V7 this query that
> worked on v5r4 returns"
>
> OLE DB provider "MSDASQL" for linked server "MOPICO" returned message
> "Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.".
> OLE DB provider "MSDASQL" for linked server "MOPICO" returned message
> "[IBM][iSeries Access ODBC Driver]String data right truncation.".
> OLE DB provider "MSDASQL" for linked server "MOPICO" returned message
> "[IBM][iSeries Access ODBC Driver]Column 9: CWB0111 - A buffer passed to a
> system call is too small to hold return data".
> Msg 7311, Level 16, State 2, Line 1
> Cannot obtain the schema rowset "DBSCHEMA_INDEXES" for OLE DB provider
> "MSDASQL" for linked server "MOPICO". The provider supports the interface,
> but returns a failure code when it is used.
>
> SELECT
>   WEIGHT_TICKET AS WLOAD
>   ,VENDOR AS WVEND
>   ,VNDNAM AS WNAME
>   ,VENDOR_BOL AS WBOL
>   ,SUM(CLITO) AS WQTY
>   FROM [xxxxx].[dbo].[tblWeightTicket]
>   JOIN xxxxx.xxxxx.xxxxxx.CUKE004
>   ON weight_ticket = cload
>   JOIN xxxxx.xxxxx.xxxxx.AVM
>   ON CVEND = VENDOR
>   WHERE MODIFIED_DATE > DATEADD(MONTH,-6,GETDATE())
>   GROUP BY WEIGHT_TICKET,VENDOR,VNDNAM,VENDOR_BOL
>   ORDER BY WEIGHT_TICKET
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.