Re: MSSQL and AS400 Query

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.