Incorrect Schema when Updating Datasource Location

[Former Member]

I am using SAP Crystal Reports 2011 and am trying to update the datasource locations on an existing report to a new database. I am using a DB2 database and the datasource connections are configured through ODBC.

In the report, I go to Database > Set Datasource Location > Select the Connection I want to update to > Then click update. The update process works correctly, but the tables that were updated are using the wrong Owner (schema).

Is there something in DB2 we need to specify to use to correct schema? What is the best way to update the "owner" of each table without manually having to update each table individually. We have thousands of reports, so updating the tables individually will be very time consuming.

EDIT: As the posted answer suggests, I updated each table individually rather than the entire DSN. This outputs the same result. The tables have a schema of 'B' when they should be schema 'A'. Interestingly enough, the user who is connecting to the DSN doesn't even have access to schema 'B'.

[former_member11696]

Create 2 DSN's, one for DB Schema A and one for DB Schema B and then set location to the other one and see if that works.

CR fully supports Permissions so if the user does not have permissions to access the tables for Schema B CR will not update the connection info.

Managing security is your responsibility. Do not expect CR to do that for you, it's not capable.

Don

[Former Member]

The user does not have permission to read the tables in schema B and that is what we are seeing when attempting to run the report, so I can confirm that aspect is working correctly.

My current DSN is configured to use schema 'A' with the CLI Parameter "CurrentSchema" with a value of 'A'. However, this does not solve the issue either. Updating from the old DSN to the new DSN results in the tables pointing to schema 'B'.

I do not see any areas to specify a schema with Crystal Report Designer, so I am assuming this would have to exist in the DSN like I specified above.

[former_member292966]

Hi Zachary,

After you do the Set Datasource Location, go into Database | Show SQL Query. Make sure the schema is correct in the SQL. Set Datasource Location doesn't change the alias but will change the schema if you logged on properly.

You can change the Schema by going into the Database Expert and right-clicking on the table and rename it in the Selected Tables window.

Good luck,

Brian

[Former Member]

The schema name is not correct when I go into Database | Show SQL Query. It is still showing as schema 'B', when I need schema 'A'.

I went into Database | Database Expert and tried to rename the table in the Selected Tables window but that did not work either. Trying to rename the tables to 'A.TABLE' output the following: "Invalid Argument provided. Details: The alias requested 'A.TABLE', contains a combination of characters which is not considered to be valid."

[former_member11696]

mmm... I'll get one of the Nexus Rep's to test this. It is likely because User does not have access to the original.

Go into Report options and uncheck all Verify options and then set location again.

See if that works...

[former_member11696]

Hi Zachary,

When updating the connection try clicking on each table and not just the new DSN.

Don

[Former Member]

I updated each table individually rather than the entire DSN. This outputs the same result. The tables have a schema of 'B' when they should be schema 'A'. Interestingly enough, the user who is connecting to the DSN doesn't even have access to schema 'B'.