Skip to Content

Problem with SAP Crystal Reports being used with SAGE 300 ERP 2012 on Oracle 11g on RHEL Linux

Dear all

I am a contractor at Achievement Awards Group (AAGroup) in Cape Town and am responsible for integrating their specialist incentive and loyalty systems with Accpac. I also do a lot of Accpac support and have been involved with Accpac at AAGroup since 2004.

We did the upgrade from Accpac version 6.0 to v 6.1 (now called Sage 300 ERP 2012) on the 26th October and since then we have not been able to run most of the reports in AR and AP. These are the reports that run inside Sage and which call Crystal Runtime v 2011 and then connect to the database and execute.

We keep getting the attached errors no matter what we do. And we have put over 80 hours into it so far and we have 17 Accpac users without reports and an SMME having to do exports and Excel based reports for 3 and a half weeks now.

Is there anyone out there who has experienced this problem with Crystal Reports and Oracle?

Your help will be extremely appreciated as SAP and SAGE seem unable to help us and have told us to move to SQL-Server. This is not possible in such a large Oracle environment with quite a lot of interconnectivity built from AAGroup's systems to date in Accpac. In any case SAGE's upgrade path shows that they support Oracle 11g and they should have sent someone to help us.

Regards

David

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Best Answer
    Nov 21, 2013 at 08:11 PM

    Dear Abhilash and Don.

    Thank you for your quick replies. But they weren't helpful.

    Actually the problem was that SAP Crystal Reports has its own ODBC driver for Oracle and doesn't use the Standard Oracle ODBC driver. It's a real pity that it has taken us 4 weeks of Reporting Downtime to find this out!!!

    Sage should have known that you have changed the ODBC connector and you should have been able to point me in the right direction!

    Regards

    David

    Add comment
    10|10000 characters needed characters exceeded

    • Once again, Debug with Sage, We don't have their DB or how they configure the driver or which ones their reports support.

      If Sage has not tested those ODBC Drivers then I assume you can't use them.

      Sorry but nothing we can do, Sage has to debug and support your configuration. They may be over writing your connection properties with their own so no matter what you use it always rolls back to what they hard coded in their app.

      Even if we could duplicate the issue, we can't change their code.

      Error message appears to be telling you the field is wrong or the values is not being converted properly.

      Open CR Designer, if you have it and test the formula, try and find out what part is causing the error, likely it's expecting a string and a number is entered or somethign like that...

      Don

  • Nov 20, 2013 at 01:52 PM

    Hi David,

    I'm guessing this is something SAGE will have to resolve.

    @Don Williams or @Ludek Uher might have a better answer I suppose.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 20, 2013 at 03:22 PM

    Hi David,

    Sage is an OEM Partner so you need to contact Sage for Support with database issues. If it's basic report design problems then anyone can help but for DB issues, since it's unique to their install and reports, they have to help you.

    If you have CR Designer installed and you refresh does it give the same error? If you Verify Database does that fix the problem? This info may help Sage debug the problem for you.

    Thank you

    Don

    Senior Support Engineer

    AGS Primary Support

    Global Support Center Canada

    SAP Canada, Inc.

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 12, 2013 at 08:45 PM

    This morning I had a brain wave. Considering that when I open the reports in Crystal Reports, it defaults to SAMLTD, I decided to ask Kris (Oracle DBA) to create a SAMLTD tablespace and to create SAMSYS and SAMLTD users/schemas inside it, and to give these users only the 4 rights you told us, ie Connect, Resource, Create Any Table, Unlimited Tablespace, and no DBA privileges. This on our Dev Server, so that I could load ACHGRP into SAMLTD and see if I could run reports.

    Almost every morning for the past week, I've been backing up the database using Database Dump and restoring it on my SQL Server machine so that I can run reports for the Accounts Department.

    So I restored (Database Load) ACHSYS to SAMSYS and ACHGRP to SAMLTD. And the reports worked!

    Kris then suggested we load ACHCON into SAMCON (also in the SAMLTD tablespace) and this also worked!

    Kris and I then created the same environment on the Test server and this also worked.

    Note that the Test and Dev servers have got different character sets, and I wanted to test this before making the system Live.

    So we created SAMCON on the Test server and imported it and the reports work for SAMCON and SAMLTD (AAGroup).

    Then on the Live server, I then checked that all the ACH% companies use ACHSYS and all the SAN% companies use SANSYS and then I asked Kris to Drop the SAMLTD tablespace and recreate it at about 9GB with all 7 main companies so that we could load all the companies onto the Live server. We loaded ACHSYS into SAMSYS and ACHGRP into SAMLTD and reports worked. We then loaded ACHCON into SAMCON and both companies work. I then loaded all the other companies and they all work.

    On Friday 13th we will add the SAM% ODBC DSN's to 17 Sage 300 users, and show them how to run their own reports for the SAM databases, but they will still need to work in the ACH databases from a posting, etc, point of view, until Tuesday morning (Monday is a public holiday).

    We will spend the day on Friday 13th testing and ensuring that everything works and over the weekend I will modify all the front ends and Oracle stored procedures and packages that use ACHGRP, etc, to work with SAMLTD, etc. One problem we need to resolve on Friday morning is user permissions.

    So the learning is:

    1) The character set makes no difference

    2) Having all the Users (Schemas) in a single tablespace makes no difference, which is how we had the system from 2004 until 2 months ago

    3) The Tablespace MUST be called SAMLTD

    4) SAMSYS and SAMLTD MUST exist.

    Add comment
    10|10000 characters needed characters exceeded