32

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) ?

JSR
  • 6,106
  • 2
  • 20
  • 24
  • The answer should have really been to restore to the former database to the new database using a different name. Then the new database should have used the same database that all the reports were using. If the schema are differently, you'd only need to edit those reports where the schema different, but not the datasource location. – Sun Apr 30 '16 at 18:04

2 Answers2

58

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.

  1. Change the Datasource Connection:
  2. From the Current Data Source list (the top box), click once on the datasource connection that you want to change.
  3. In the Replace with list (the bottom box), click once on the new datasource connection.
  4. Click Update.
  5. Change Individual Tables:
  6. From the Current Data Source list (the top box), expand the datasource connection that you want to change.
  7. Find the table for which you want to update the location or name.
  8. In the Replace with list (the bottom box), expand the new datasource connection.
  9. Find the new table you want to update to point to.
  10. Click Update.
  11. Note that if the table name has changed, the old table name will still appear in the Field Explorer even though it is now using the new table. (You can confirm this be looking at the Table Name of the table's properties in Current Data Source in Set Datasource Location. Screenshot https://i.stack.imgur.com/NRr4G.png) It's possible to rename the old table name to the new name from the context menu in Database Expert -> Selected Tables.
  12. Change Subreports:
  13. Repeat each of the above steps for any subreports you might have embedded in your report.
  14. Close the Set Datasource Location window.
  15. Any Commands or SQL Expressions:
  16. Go to the Database menu and click Database Expert.
  17. If the report designer used "Add Command" to write custom SQL it will be shown in the Selected Tables box on the right.
  18. Right click that command and choose "Edit Command".
  19. Check if that SQL is specifying a specific database. If so you might need to change it.
  20. Close the Database Expert window.
  21. In the Field Explorer pane on the right, right click any SQL Expressions.
  22. Check if the SQL Expressions are specifying a specific database. If so you might need to change it also.
  23. Save and close your Formula Editor window when you're done editing.

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.

Todd Walton
  • 1,083
  • 11
  • 24
  • but if you do like this, then all the new datasource fields will be in string format – King of kings Feb 16 '15 at 11:09
  • If you're working in Visual Studio, after you do this, you may want to save,close, and reopen Visual Studio. I just had VS2010 crash about 20 minutes after I did this update (which worked great initially).. but when it crashed.... I got "Failed to load dataset because of the following error: Object reference not set to an instance of an object." Might not be related at all... but it might save some pain...I allowed VS to reopen automatically the first time... probably not a good idea either... I might have been better off shutting down VS down and reopening manually... – Danimal111 May 06 '15 at 17:42
  • I use this one in crystal report 9 but the new field I added in the database didn't reflect to my datasource in crystal report?Any suggestion why new field didn't display? I post a question here : http://stackoverflow.com/questions/30455483/how-to-update-datasource-in-crystal-report-9 – Datz Me May 26 '15 at 10:19
  • this post may be old but would like to ask, would the dynamic datasource affect any functions within the sql expression field? because i'm getting ERROR:System.Runtime.InteropServices.COMException (0x80042018): The table %1 does not exist in the document running from ASP.net – RoMEoMusTDiE Mar 16 '17 at 03:50
  • 1
    Just a note for this great answer: (In case of `Command`) If the command is nothing but an executed stored procedure (i.e. Table Type = Stored Procedure), then you can follow the same steps given above for changing individual tables (#2). The difference is in *Current Data Source list* (the top box) you need to point to Command and in *Replace with list* (the bottom box) select the stored procedure which the command is executing and the update button will be enabled. [See this](https://i.stack.imgur.com/CP67K.png). – Himanshu Feb 19 '18 at 06:22
7

Choose Database | Set Datasource Location... Select the database node (yellow-ish cylinder) of the current connection, then select the database node of the desired connection (you may need to authenticate), then click Update.

You will need to do this for the 'Subreports' nodes as well.

FYI, you can also do individual tables by selecting each individually, then choosing Update.

craig
  • 25,664
  • 27
  • 119
  • 205
  • 1
    Just tried this, the report is still querying DB_A. Also, when I select "Show SQL Query" from the Database menu and it shows "SELECT col1, col2 FROM [DB_A].[dbo].[Table]". What I need is for it to do "SELECT col1, col2 FROM [DB_B].[dbo].[Table]" – JSR Nov 14 '11 at 22:23
  • Are you using the native driver, ODBC, or OLEDB? – craig Nov 15 '11 at 00:31
  • I normally use the native driver. If I have issues switching, I'll create a temporary ODBC datasource that points to the correct location. I'll set the location to the ODBC datasource, then back to the native driver with the correct schema owner. – craig Nov 15 '11 at 00:34
  • 1
    Finally got it worked out ... it seems that the user id specified in the new connection must not have any access to DB_A in order for Crystal to change the query to select from DB_B tables. Only then will Crystal change the database name in the Sql query. – JSR Nov 15 '11 at 22:10