Re: MSSQL and AS400 Query

Perhaps the origin for the issue is described by the IBM i Memo to Users 7.1 [the MTU] whereby "SQL warnings now reported to applications" could be the origin for the issue? See any of the following links:

http://www-912.ibm.com/s_dir/sline003.NSF/7f02fa4a8460c21f86256c450052b184/32745592b51aaa608625770500718012?OpenDocument

http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/rzaq9/rzaq9.pdf
_i IBM i Memo to Users 7.1 (February 2013 update) i_
"... SQL warnings now reported to applications . . 22
...
_SQL warnings now reported to applications_
In earlier releases of IBM i, warning conditions which were encountered during the processing of query result sets were not always reported back to the application as they should have been. These warnings,
which include but are not limited to, SQLSTATE 01003 - ’Null values were eliminated from an argument of an aggregate function’, and SQLSTATE 01506 -’An adjustment was made to a DATE or TIMESTAMP value to correct an invalid date resulting from an arithmetic operation’, are now returned to the application. As a result of this change, ODBC and CLI applications encountering these warnings receive a SQL_SUCCESS_WITH_INFO return code on the fetch API request rather than a SQL_SUCCESS return code. ..."

I realize that just knowing that, does not directly provide a means to resolve the issue. But hopefully that information can assist in either effecting a circumvention or finding a resolution. I suppose for example, if the issue is with a date adjust in the UDF that is used in the selection, versus anything with the result set, then perhaps the warning can be cleared in the UDF if\when the UDF does is not concerned about the condition being diagnosed\warned?

Regards, Chuck

On 07 Apr 2013 07:10, Billy Waters 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