Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2005
    Posts
    9

    Unanswered: Migration from Crystal Reports 8.5 to 10

    I am trying to migrate Crystal Report 8.5 to 10. In one of the reports I am calling a stored procedure which is inserting to a table and making a cursor and returning.

    It is working fine for Crystal Reports 8.5. When I tried to execute it from Crystal Reports 10 it is giving me the following error. "Inserted value too large for Column"

    I am not sure why it is working fine for Crystal Reports 8.5 and not working for Crystal Reports 10

  2. #2
    Join Date
    Oct 2003
    Posts
    357
    Before running check verify database option
    Madhivanan

    Failing to plan is Planning to fail

  3. #3
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112
    If verifying the database doesn't work ...


    What is the variable type for the column giving the problem? E.g. if using SQL Server you might have a 8-byte, 4-byte, 2-byte or 1-byte integer. There could be a difference in assumptions made by CR 8.5 and CR 10 regarding variable type. CR 8.5 and CR 10 could perhaps also be using a different data source provider?

  4. #4
    Join Date
    Mar 2005
    Posts
    9
    Quote Originally Posted by Madhivanan
    Before running check verify database option
    What options in the database need to be checked?

  5. #5
    Join Date
    Mar 2005
    Posts
    9
    Quote Originally Posted by bcummings
    If verifying the database doesn't work ...


    What is the variable type for the column giving the problem? E.g. if using SQL Server you might have a 8-byte, 4-byte, 2-byte or 1-byte integer. There could be a difference in assumptions made by CR 8.5 and CR 10 regarding variable type. CR 8.5 and CR 10 could perhaps also be using a different data source provider?
    The variable type is CHAR(10) and inserted value is DATE but the value it is having is '20-MAR-05' which can fit into CHAR(10).

  6. #6
    Join Date
    Oct 2003
    Posts
    357
    Open the report
    In the menu goto Database-->Verify Database
    Madhivanan

    Failing to plan is Planning to fail

  7. #7
    Join Date
    Mar 2005
    Posts
    9
    Quote Originally Posted by Madhivanan
    Open the report
    In the menu goto Database-->Verify Database
    I tried that also. It is giving me same error.

  8. #8
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112
    Are you using Oracle and getting "ORA-01401: Inserted value too large for column", or is it another database showing the error, or is it Crystal Reports itself that throws the error?

  9. #9
    Join Date
    Mar 2005
    Posts
    9
    Quote Originally Posted by bcummings
    Are you using Oracle and getting "ORA-01401: Inserted value too large for column", or is it another database showing the error, or is it Crystal Reports itself that throws the error?
    Let me explain you. There is a stored procedure which is getting called by Crystal reports 10. The stored procedure is doing the following things.

    1. Insert records (which satisfy some criteria) into a temp table.
    2. Convert the inserted data into a cursor.
    3. Return the cursor to the Crystal reports.

    But it is failing in inserting (Step 1) as there is a field "LAST_CARD_MAILED_DATE" which is declared as CHAR(10) in temp table ", but the value which is getting inserted in it is of type DATE. So report is giving ORA-01401 error and coming out.

    But this stored procedure is getting execute successfully if it is getting called for Crystal Reports 8.5. I would like to know why it is working fine with Crystal Reports 8.5 and not with Crystal Report 10.

  10. #10
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112
    OK, now I get it!


    I tried the same thing, using SQL Server's Northwind database since I don't have Oracle. I only get something similar if I have a date string that is physically too long (e.g. 01-MAR-2005 instead of 01-MAR-05).

    What kind of database provider / data source are you using (OLEDB, ODBC, etc.). I'm wondering if Crystal modifies the string it passes to the database in some way.

    Can you also post the first few lines of the stored proc (so that I can see the variable declarations, etc.)?

  11. #11
    Join Date
    Mar 2005
    Posts
    2
    Hello,
    I am also calling a stored proc and getting back a cursor. It works fine in Crystal Report but when I am displaying it through JSP, it is saying Unexpected Engine Query Error.
    Can some one pls. advise how can I pass return parameter through JSP?
    Thanks in advance.

    Quote Originally Posted by mahendra_r1
    Let me explain you. There is a stored procedure which is getting called by Crystal reports 10. The stored procedure is doing the following things.

    1. Insert records (which satisfy some criteria) into a temp table.
    2. Convert the inserted data into a cursor.
    3. Return the cursor to the Crystal reports.

    But it is failing in inserting (Step 1) as there is a field "LAST_CARD_MAILED_DATE" which is declared as CHAR(10) in temp table ", but the value which is getting inserted in it is of type DATE. So report is giving ORA-01401 error and coming out.

    But this stored procedure is getting execute successfully if it is getting called for Crystal Reports 8.5. I would like to know why it is working fine with Crystal Reports 8.5 and not with Crystal Report 10.

  12. #12
    Join Date
    Mar 2005
    Posts
    9
    Quote Originally Posted by bcummings
    OK, now I get it!


    I tried the same thing, using SQL Server's Northwind database since I don't have Oracle. I only get something similar if I have a date string that is physically too long (e.g. 01-MAR-2005 instead of 01-MAR-05).

    What kind of database provider / data source are you using (OLEDB, ODBC, etc.). I'm wondering if Crystal modifies the string it passes to the database in some way.

    Can you also post the first few lines of the stored proc (so that I can see the variable declarations, etc.)?
    Please find the stored procedures.

    CREATE OR REPLACE PROCEDURE R_P_CEBCARDS(R_RET IN OUT R_PKG_ECAM_EORS.R_CUR_CEBCARDS,ACCT_ID IN CHAR)
    AS
    l_Acct_id CHAR(10);
    l_Card_No CHAR(10);
    l_Card_Id CHAR(10);
    l_Rpt_name1_Ind CHAR(25);
    l_Contact_Id CHAR(10);
    l_Rpt_Name1 CHAR(25);
    l_Card_Name1 CHAR(25);
    l_Card_Name2 CHAR(25);
    l_Parent_Acct_Id CHAR(10);
    l_temp_id CHAR(10);
    l_Auth_Grp_Id CHAR(10);
    l_Hq_Acct_Id CHAR(10);
    l_Hq_Acct_name1 CHAR(25);
    l_prod_Id CHAR(5) ;
    l_MCN CHAR(11);
    l_Finan_Acct_Id CHAR(10);
    l_Finan_Acct_Type CHAR(2);
    l_CC_No VARCHAR2(24);
    l_Count NUMBER(5);
    l_Report_Id NUMBER(38);
    l_LDN CHAR(10);
    l_Int_Id CHAR(10);
    l_Last_Card_Mail_Date DATE;
    l_Add_Date DATE;
    l_Media CHAR(1);
    l_Exp_Date CHAR(10);
    l_Status CHAR(1);
    l_Seed BINARY_INTEGER;

    CURSOR Cur_Cust(Cur_Acct_Id CUST.CUST_ACCT_ID%TYPE) IS
    SELECT CUR_ACCT_ID,
    CU.ACCT_NAME1,
    CU.MCN,
    CU.PROD_ID,
    C.ACCT_ID,
    C.CARD_NO,
    c.RPT_NAME1_IND,
    c.FINAN_ACCT_ID,
    c.FINAN_ACCT_TYPE,
    C.CARD_NAME1,
    C.CARD_NAME2,
    C.CONTACT_ID,
    C.LAST_CARD_MAILED_DATE,
    C.ADD_DATE_TIME,
    C.STATUS
    FROM
    CARD C,
    AUTH_GRP A,
    CUST CU
    WHERE
    C.AUTH_GRP_ID=A.AUTH_GRP_ID AND
    A.CUST_ACCT_ID=CU.CUST_ACCT_ID AND
    (CU.CUST_ACCT_ID=UPPER(CUR_ACCT_ID) OR
    (CU.MCN=UPPER(CUR_ACCT_ID) AND
    (CU.PROD_ID=104 OR
    CU.PROD_ID=204 OR
    CU.PROD_ID=110)));

    CURSOR Cur_Int(Cur_Acct_Id CUST.CUST_ACCT_ID%TYPE) IS
    SELECT C.ACCT_ID,
    C.CARD_NO,
    c.RPT_NAME1_IND,
    c.FINAN_ACCT_ID,
    c.FINAN_ACCT_TYPE,
    C.CARD_NAME1,
    C.CARD_NAME2,
    C.CONTACT_ID,
    C.LAST_CARD_MAILED_DATE,
    C.ADD_DATE_TIME,
    C.STATUS
    FROM CARD c
    WHERE c.ACCT_ID=UPPER(Cur_Acct_Id);

    BEGIN
    SELECT TO_NUMBER(SUBSTR(CURRENT_TIMESTAMP,20,6)) INTO l_seed FROM DUAL;
    DBMS_RANDOM.INITIALIZE(l_Seed);
    l_Report_Id:=DBMS_RANDOM.RANDOM();
    DBMS_RANDOM.TERMINATE();
    SELECT COUNT(*) INTO l_Count FROM CUST WHERE CUST_ACCT_ID=ACCT_ID OR (MCN=UPPER(ACCT_ID) AND (PROD_ID=104 OR PROD_ID=204 OR PROD_ID=110));
    IF l_Count>0 THEN
    OPEN Cur_Cust(Acct_Id);
    LOOP
    FETCH Cur_Cust INTO l_Hq_Acct_Id,l_Hq_Acct_Name1,l_MCN,l_Prod_Id, l_Acct_Id,l_Card_No,l_Rpt_Name1_Ind,l_Finan_Acct_I d,
    l_Finan_Acct_type,l_Card_Name1,l_Card_Name2,l_Cont act_Id,l_Last_Card_Mail_Date,l_Add_Date,l_Status;
    INSERT INTO R_TBL_CEBCARDS
    VALUES (
    l_Hq_Acct_Id,
    l_Hq_Acct_Name1,
    l_MCN,
    l_Prod_Id,
    l_Acct_Id,
    l_LDN,
    l_Card_no,
    l_Rpt_Name1,
    l_CC_No,
    l_Card_Name1,
    l_Card_name2,
    l_Last_Card_Mail_Date,
    l_Add_Date,
    l_Status,
    l_Media,
    l_Exp_Date,
    ACCT_ID,
    l_Report_Id
    );
    COMMIT;
    END LOOP;
    CLOSE Cur_Cust;
    OPEN R_RET FOR SELECT * FROM R_TBL_CEBCARDS WHERE REPORT_ID=l_Report_Id ORDER BY Exp_Date;
    COMMIT;
    DELETE R_TBL_CEBCARDS WHERE REPORT_ID=l_Report_Id;
    COMMIT;

  13. #13
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112
    OK, I'm really 'clutching at straws' now. You might try posting the same question as a new thread on the Oracle forum.


    What would make most sense to me would be if you were running this on 2 PCs (1 with Crystal 8.5, the other with Crystal 10). If the local date formats are not identical, or if Oracle is configured differently on the PCs, PERHAPS the date '10-MAR-05' on 1 PC is '10-MAR-2005' on the other?

    So if your problem is happening on a single PC with the only change being the Crystal version, I am out of ideas!

    Good luck,


    Barry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •