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

[Former Member]

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

[Former Member · Accepted solution]

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

[former_member11696 · Accepted solution]

Hi David,

Sage has their own ODBC driver also. Actually we don't have our own, it's a third party driver from Data Direct that we ship for your convenience if you don't have one. They are there for the creators of reports, not using an OEM Partners canned reports or possibly a DB platform they don't support.

We can't see which driver is used by looking at the report, how you or Sage or anyone else connects is not something we can magically find out unless you tell us or You tell Sage which driver you are using. Without details we can't just tell you how to "fix it".

Any reason why you never used the same connection DSN as what Sage used?

It's a real pity that you never included enough info to be of any help. The word "Problem" is not very descriptive of an issue. Even a simple error message would have been extremely helpful and pointed us in the right direct. I assume you were getting some database error message.

Next time you run into issues put something in the post other than "I have a report and it doesn't work, what is wrong"....

Sage is obligated to support Crystal Report issues, if the first person you talked to did not help you you should have asked them to escalate the issue to a more senior Support Rep.

But it could be, and your info suggests, you are using a version of Crystal Reports that Sage doesn't support.

So before blaming someone else have a look at the info you are posting and if there is sufficient info for anyone to help without having to ask for more.

Good luck

Don

[Former Member · Accepted solution]

Thanks Don. Sorry if you saw me as blaming you. I take full responsibility for not starting from first principles. Check the cables. Check the tns entries. Check the ODBC connectors.

But you see we already have Sage running since 2004 using the Oracle ODBC connector. And so we assumed (incorrectly) that SAP uses the same connector. Up until Sage v6, Sage used the Oracle ODBC connector. Not sure how we were meant to know it had changed?

And if you read the title of this thread, it says: "Problem with SAP Crystal Reports being used with Sage 300 ERP 2012 on Oracle 11g on Red Hat Enterprise Linux" and I referred to Crystal Runtime v2011 in the text of my message. Not sure if this says "nothing" about our environment?

Is it possible that this line 'Next time you run into issues put something in the post other than "I have a report and it doesn't work, what is wrong"….' is from an auto responder and relates to a query from someone else?

I also attached a screen shot of the error message ("a simple error message") we have had for the past 4 weeks. Did you see it?

Regards

David

[Former Member · Accepted solution]

Just a note re the SAP Crystal Reports 2011 (onwards) Driver.

It is "CR Oracle ODBC Driver 7.0.1" and choose "Client Version 10gR1 and later" in the DSN dialog.

[former_member11696 · Accepted solution]

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

[Former Member]

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.

[former_member11696]

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.

[abhilash_kumar]

Hi David,

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

or might have a better answer I suppose.

-Abhilash