Re: MSSQL and AS400 Query

[CRPence <CRPbottle@xxxxxxxxx>]

  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