I have a Crystal Reports 2008 user that has over 100 custom developed reports.
The reports all query Sql Server databases (SQL 2005).
This database server is getting replaced with a new system (running SQL 2008 R2) and the existing databases will be moved to the new server.
The new database server will have a different name (which I can address via the Crystal Reports Connections),
however, one of the applications is also being upgraded at the same time. The old database (DB_A) will be restored on the new server for historical reporting and a new database (DB_B) will be created.
The new DB_B will have a very similar schema, so I would expect that most of the Crystal Reports should be able to run against the new DB_B with little or no modification other than pointing the report definition at the new DB_B.
Of course, the majority of my users custom developed reports query against DB_A.
My question is: How do I modify existing Crystal Reports files to point at the new database name (DB_B) instead of the old database (DB_A) ?
Use the Database menu and "Set Datasource Location" menu option to change the name or location of each table in a report.
This works for changing the location of a database, changing to a new database, and changing the location or name of an individual table being used in your report.
To change the datasource connection, go the Database menu and click Set Datasource Location.
And try running the report again.
The key is to change the datasource connection first, then any tables you need to update, then the other stuff. The connection won't automatically change the tables underneath. Those tables are like goslings that've imprinted on the first large goose-like animal they see. They'll continue to bypass all reason and logic and go to where they've always gone unless you specifically manually change them.
To make it more convenient, here's a tip: You can "Show SQL Query" in the Database menu, and you'll see table names qualified with the database (like "Sales"."dbo"."Customers") for any tables that go straight to a specific database. That might make the hunting easier if you have a lot of stuff going on. When I tackled this problem I had to change each and every table to point to the new table in the new database.