Topic
2 replies Latest Post - ‏2012-11-19T14:47:26Z by DanielWagemann
venkat_Jey
venkat_Jey
92 Posts
ACCEPTED ANSWER

Pinned topic Error while using a Stored Procedure from a Report Studio package.

‏2012-11-18T08:13:31Z |
Environment :
Cognos 10.1.1 DB : DB2 9.7

I have a stored procedure in my model which accepts two input parameters. I already have a list report which has lot of filters and gives few rows of data . I need to use two of those columns from the list report , to send it as input parameter to the stored procedure which will insert those two column in the back end DB.

SO, I dragged query container into this list and then put an 'ID' data item from my SP into this nested query container. The query container is a list. Then I set up the master detail relationship between the two queries to feed the data from the first list into the SP.

SP :

CREATE PROCEDURE SKO.StoredProcedure5 (IN TriggerDesc VarChar(100), IN TargetPerson VarChar(60))
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE CURSOR1 CURSOR WITH RETURN FOR SELECT * FROM SKO.TRIGGERFEEDBACKDATA;
Insert into SKO.TRIGGERFEEDBACKDATA (DateTriggerFired, TriggerDesc, Target) Values (CURRENT_DATE,TriggerDesc,TargetPerson);
COMMIT;
OPEN CURSOR1;
END

So It was working fine taking values from the two columns and writing it back to the database.

Suddenly it stopped working and started giving errors. But the report doesnt give an error and when the list's Box Type property is changed to 'NONE;, The report runs fine, else it gives a following error.

I have also attached a snapshot of the error.
UDA-SQL-0114 The cursor supplied to the operation "APICursor::OpenResult" is inactive.UDA-SQL-0107 A general exception has occurred during the operation "execute".IBMCLI Driver CLI0109E String data right truncation. SQLSTATE=22001RSV-SRV-0042 Trace back:RSReportService.cpp(722): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(263): QFException: CCL_RETHROW: RSReportServiceMethod::process(): promptPagingForward_RequestRSASyncExecutionThread.cpp(808): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(260): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): promptPagingForward_RequestRSASyncExecutionThread.cpp(864): QFException: CCL_RETHROW: RSASyncExecutionThread

Im very much surprised to see why it wouldn't work suddenly.
Updated on 2012-11-19T14:47:26Z at 2012-11-19T14:47:26Z by DanielWagemann
  • venkat_Jey
    venkat_Jey
    92 Posts
    ACCEPTED ANSWER

    Re: Error while using a Stored Procedure from a Report Studio package.

    ‏2012-11-18T08:14:19Z  in response to venkat_Jey
    I am also attaching the snapshot of my SP setting in the FM.

    Any help would be greatly appreciated.
    • DanielWagemann
      DanielWagemann
      411 Posts
      ACCEPTED ANSWER

      Re: Error while using a Stored Procedure from a Report Studio package.

      ‏2012-11-19T14:47:26Z  in response to venkat_Jey
      The CLI0109E String data right truncation error is a DB2 client side error. This error usually appears if you are inserting values into a table where size of the columns do not match the size of the insert. Ensure your table column sizes match those that you have specified for the import.