Accessing Data moved to a new Oracle Schema

[SonOfZeus]

As the company has adapted to changes for SOX and creation of new applications for other divisions, we've encountered an issue. In seperating data, we've taken an application from Schema1 and moved it to Schema2. The problem I have now, is how do I change the Crystal Reports to reflect this.

 

[synapsevampire]

You post your version of Crystal and the connectivity being used, technical information is always key here, not so much why you decided to change things.

If it's ODBC, change the ODBC setting, if native, you'll need to alter the connection using the Database->Set Loaction, as with other forms of connectivity.

There are mass updating tools, such as:

http://www.softwareforces.com/?pid=ri3ps&ad=tekrips:2

And Apos has something for Crystal Enterprise/BOE.

Again, post your environment and requirements for tailored answers.

-k

 

[SonOfZeus]

I'm using Crystal 10 and the reports are independent of an application, thus I'm able to test and manipulate the report directly. I'm using the Microsoft ODBC for oracle driver to make the connection. The report sits on a RAS server, the problem is I have to change the recordsource from Schema1.StoredProc to Schema2.StoredProc on the same Oracle instance.

 

[synapsevampire]

btw, you're not supposed to use that connectivity with Crystal, use the Crystal supplied Oracle ODBC driver (make sure you select procedure returns results), or better yet, use the Oracle native connectivity supplied by Crystal. You'll see it listed as Oracle server.

Anyway, just use the Database->Set Location and point to the new SP.

-k

 

[SonOfZeus]

This doesn't work as the service is the same for both Schemas. Thus it can't find Schema1.Proc for the datasource, and wants to remove the source and it's fields. I'll look into the connectivity supplies by Crystal, I didn't look for a driver that isn't installed with the software.

 

[synapsevampire]

It is installed with the software. But the connectivity you're using was NOT installed by Crystal, that's Windows stuff...

And it will show other Oracle schemas in Crystal, you're mistaken, it depends on what you have permission to and how your connectivity is configured.


I'd uggest that you speak with your Oracle dba and tell them that you can't see the other schema.

-k

 

[SonOfZeus]

I've had the DBA and can see the procedure in both Schemas. Unfortunately the Oracle Account isn't defaulted to a Schema and thus needs Crystal to specify which one to use. Right now it will be StoredProc one and if the name exists in both Schemas it doesn't run. How do I change the source to a specific Schema without having to replace every field on the report as well

 

[synapsevampire]

I don't think that Crystal will specify it, the dba can though.

Then just use Database->Set Location.

-k

 

[SmpTraining]

I have the same problem SoZ.

Crystal Version: 9.2.0.448 Developer
Oracle Version: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
ODBC Driver: Oracle ODBC Driver (v8.01.06.00)

We have two schemas : EMSMAIN and EMSTEST

I've written the reports in v8.5. and am now upgrading them to v9. Opened them in v9 and they run fine against EMSTEST. Now want to run them against EMSMAIN.

In the Set DataSource Location screen you can select an individual EMSTEST table from the report and select the EMSMAIN equivalent in the bottom screen. This will work but is going to be a time consuming task.

Any way of changing this at the report level would be very welcome.

Steve

Steve Phillips, Crystal Trainer/Consultant

http://www.smptraining.co.uk

 

[SmpTraining]

Sorry guys - I've done a bit more searching and the solution to my problem seems to be to apply the latest Service Pack.

Steve Phillips, Crystal Trainer/Consultant

http://www.smptraining.co.uk

 

[SonOfZeus]

I've found the solution to the problem. I was trying to use database -> location and it didn't let me control the schema. What I found is on the Field Explorer, you can click on the Table/Stored Procedure and it allows you to remap to the correct location (new Schema) I've done this for the reports and all is well. Thanks for all the feedback.