Re: MSSQL and AS400 Query

[CRPence <CRPbottle@xxxxxxxxx>]

  Seems I had managed to drop the following link and text from my prior 
reply.  The text from that old message should have been included, 
because the issue in that old message was more like the first message 
"String data right truncation" described as received from ODBC; i.e. the 
first error described in the OP for this thread.

http://archive.midrange.com/rpg400-l/201110/msg00186.html
"... sqlstate [is] a '01004' to inform me that 'The value of a
string was truncated when assigned to another string data type with a
shorter length.' ..."

  I had mentioned the /date adjustment/ as an example instead of the 
string truncation, only because I could not figure out to what "Column 
9" referred.  Thinking again about the given SELECT statement, I suppose 
if each column\expression in the select-list were null-capable, then the 
9th reference variable [each expression and then each with an indicator 
variable] could be for the SUM aggregate expression.  Thus if the FETCH 
of the summed column CLITO was into a string variable that was not large 
enough to hold the numeric value, that could be an issue.?

Regards, Chuck

On 07 Apr 2013 11:54, CRPence wrote:
> 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?
>
> 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