cancel
Showing results for 
Search instead for 
Did you mean: 

Overridden Qualified Table Name issue

Former Member
0 Kudos

Hi All,

We are using Crystal Reports 2008 (version 12.2.0.290) to develop reports through native oracle driver (10.2.) to connect Oracle 11g Server.

We recently upgraded to Crystal Reports Server 2008 V1 SP3 to run our reports.

There are two issues we are facing at the moment.

1. If we use original db credential to run report on server, it works perfectly. However, if we use customized db login information (ie. from development to testing or production schema), the reports times out.

2. We then try to use OQTN to remove stored schema information in the report. And after type the overridden name, following error message shows: "Data Object cannot be opened/accessed", followed with "Cannot find the QE property".

This happens if we want to override name of oracle package, it does not happen when override name of views. We really struggle to identify the cause of the issue: Whether it is a limitation? or we did something wrong? And it's very time consuming if we need to reset datasource location every time when reports connect to different database schema. Therefore, any advice is more than welcome and appreciated. Thank you.

p.s. the procedures in oracle package usually call another procedures/functions within package, or a standalone function or procedure. Not sure if this affect.

Frank

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hi Frank,

Could be the DB client limitation or how CR uses it. Upgrade CR to the same SP 3 as CRS and then upgrade your Oracle Client to 11g also.

To clarify, you are using CR Native Oracle driver to connect or are you using ODBC or OLE DB?

I find for migration issues usually ODBC works the easiest due to ANSII 92 Standards. Native drivers don't always follow the rules.

Check the Platforms for when 11g was added to the supported to verify you are using correctly also.

Don

Former Member
0 Kudos

Hi Don,

Thanks heap for quick response.

We are using CR Native Oracle driver

We have upgraded CR 2008 to version (12.5.0.1190), same error message shows: "Data Object cannot be opened/accessed", followed with "Cannot find the QE property" when we tried to update OQTN.

The other thing we noticed is that after report was developed using oracle schema 'A', the catalog will have 'A' stick to it. If we log off server, re-log in using schema 'B' and run show SQL query. It still shows 'A'.'PACKAGE'.'PROCEDURE'. So the SQL seems always attached with original development schema even we tried to run it under different schema name at run time (which we presume it's the reason why it times out when we run them on CRS 2008).

There are few further questions I would like to ask:

1. There should be thousands of companies have similar environment like us: different development and production/testing schema, oracle database, CRS to run report. What's the standard procedure to deal with data-source update? Should OQTN be updated always? or change database configuration on CRS is sufficient? Although both approaches do not work for us so far. We would like to know the correct way to configure data-source to minimize repetitive work.

2. What does the error message actually mean? especially 'QE property' part.

3. The reason of using 10g instead of 11g driver is because our application is still using JAVA1.4 (believe it or not)!! and 11g does not support 1.4. But we will give it a try.

4. What is the correct syntax for OQTN? Currently we write it like this: PACKAGE_NAME.PROCEDURE_NAME

Thanks.

Frank


 

0 Kudos

Hi Frank,

Just to clarify. Are you running this in a Java app using the CR Jar's?

Typically if the DB changes you have to tell CR about it also. In the Report Designer you use the Set Location menu option, at runtime there are a few API's you can use. Not clear which SDK you are using so here's a link to our Sample Application page:

http://wiki.sdn.sap.com/wiki/display/BOBJ/Crystal+Reports+SDK+Sample+Applications

There is a JAVA deployment guide that might help also:

https://www.sdn.sap.com/irj/boc/index?rid=/library/uuid/c0afbbb9-8f1e-2b10-649f-a0d92c407657

Or/and go to help.sap.com and you'll find the latest updated SDK help files also.

QE is what the Engine uses when it detects changes to the database connection properties.

Once I know what SDK and Dev language you are using I'll move your post to the correct forum.

Don

Former Member
0 Kudos

Hi Don,

Thanks for response again. We are not using any SDK at the moment. This is our process of report development/usage:

1. Develop report using CR 2008 SP3 and/or SP5. The Oracle 11g schema stored in report will be the development schema.

2. Deploy reports on CRS 2008 V1 SP3 and/or SP5. Each report is configured to connect to either testing or production database schema.

3. User runs reports via CRS web interface.

Thus there is no SDK involved in this case. Both development (CR designer) and CRS use oracle native driver to connect (10g/11g).

Our original thought was that by configuring database in Step 2 should does the trick. Unfortunately it causes report timeout (error code: RCIRAS0244).

We then thought by using OQTN to remove development schema in Step1 should work. But it does not allow us to do so (refer to earlier post). Note: it does allow us to override if it's a view or table, but not if we try to override a procedure in an Oracle package.

We are not sure if we did something incorrectly, or there is a limitation between Oracle and CR.

I have also checked the installation guide for both SP3 and SP5 for CR 2008 and CRS 2008 V1, they both support Oracle client native driver 10g and 11g.

With Thanks,

Frank

Former Member
0 Kudos

Hi Don,

Thanks for response again. We are not using any SDK at the moment. This is our process of report development/usage:

1. Develop report using CR 2008 SP3 and/or SP5. The Oracle 11g schema stored in report will be the development schema.

2. Deploy reports on CRS 2008 V1 SP3 and/or SP5. Each report is configured to connect to either testing or production database schema.

3. User runs reports via CRS web interface.

Thus there is no SDK involved in this case. Both development (CR designer) and CRS use oracle native driver to connect (10g/11g).

Our original thought was that by configuring database in Step 2 should does the trick. Unfortunately it causes report timeout (error code: RCIRAS0244).

We then thought by using OQTN to remove development schema in Step1 should work. But it does not allow us to do so (refer to earlier post). Note: it does allow us to override if it's a view or table, but not if we try to override a procedure in an Oracle package.

We are not sure if we did something incorrectly, or there is a limitation between Oracle and CR.

I have also checked the installation guide for both SP3 and SP5 for CR 2008 and CRS 2008 V1, they both support Oracle client native driver 10g and 11g.

With Thanks,

Frank

0 Kudos

Hi Frank,

CR does pass the connection info into CRS but only what is used in the Report. So if you want to change the connection info go into the properties of the report once published to the CMC and then set the DB location using the update properties.

Just to validate the issue, if you publish 2 reports, one for each DB source and then preview or schedule once the password is set in the CMC, CR will never save the password in the report, do both reports work?

If either do not work them it's a permission issue in Oracle or the client access by Job or RAS Servers.

Thanks

Don

Former Member
0 Kudos

Hi Don,

Currently if we use development db schema to run report, it works perfectly.

Any other db schema will either cause timeout, or RCIRAS0546.

We suspect (might be wrong) that one of the following things may happen here:

1. Crystal report memorizes table prefix with development schema name. Somehow it didn't get overwritten during run time even though we deliberately replace table prefix in db configuration on CRS. (compatibility issue between Oracle and CRS perhaps)

2. Certain syntax in stored procedures may cause run time failure as CRS tries to replace table prefix.  What we mean by that is the stored procedure is correct syntax wise, but when CRS attempts to replace table prefix, it might have trouble to do so for certain syntax, which we don't know at this point. For example, usually we don't need to type package name if stored procedure is calling another one in same package, but can CR automatically figure it out it's a procedure in the same package, not a standalone procedure/function when table prefix is changed?

Schema1.PackA.procedure A calling procedure B (within PackA, and without table prefix)

Now we change database schema to Schema2

Schem2.PackA.procedure A calling procedure B <- Does CRS knows procedure B is within PackA, not standalone procedure/function?

If there is any code standard for Oracle procedure that we must follow to execute reports smoothly, please advice.

With Thanks,

Frank

0 Kudos

Hi Frank,

One thing to note here, you said:

"2. We then try to use OQTN to remove stored schema information in the report. And after type the overridden name, following error message shows: "Data Object cannot be opened/accessed", followed with "Cannot find the QE property"."

Talk to whomever wrote OQTN to remove the schema, CR does not support doing that. We need fully qualified table names.

Don

Former Member
0 Kudos

Hi Don,

Thanks for update.

Just to clarify, what we observe in 'Show SQL query' in CR developer when OQTN is not used shows:

DEVELOPMENT_SCHEMA.Package_Name.Procedure_Name

What we attempted to do (and failed) was to remove schema information so SQL will look like:

Package_Name.Procedure_Name

As you suggested that CR does not supporting doing that, perhaps this explains why error message shows up when we attempt to update OQTN for oracle packages?

Is there any other information you would like me to provide in order to resolve this issue (or another approach)? We pretty much run out of ideas at the moment.

Regards,

Frank

0 Kudos

Hi Frank,

Since you are using code to try to replace the connection try this app:

1553921 - Is there a utility that would help in writing database logon code?

Create a report off each source and then run that report through the app and it should give you the option to use the ReplaceConnection API.

Of course if you can't create a report in the designer then it's not going to help.

See this on what CR requires when using SP's and Packages also:

1181947 - Oracle Stored Procedures and Crystal Reports

Don

Answers (0)