Crystal Reports ODBC connection: the database table <tablename> cannot be found

Question

When running (or verifying the database) for a report in Crystal Reports 10, I am getting the message:

"The database table "SomeTable" cannot be found. Proceed to remove this table from the report?"

for multiple tables.

The report used to work fine. The report is getting data from multiple sources, and the missing tables are those that are coming from an ODBC connection to a SQL Server DB. I think the issue may be that when the report was created, the ODBC was pointing at a different instance of the database (same structures, just different location.)

I've checked and the report user has all the required permissions on the new database.

In Crystal, if you ignore the messages the report seems to run fine. However when deploying the report to be run from within the Crystal Report Viewer in a website, it is throwing a File I/O error.

Answer 1

This very handy blog post provides the solution: https://wisdomofsolomon.wordpress.com/2011/06/18/crystal-reports-tables-not-found-during-verify-database/

By running Show SQL Query you can see that the generated query is running SQL like

select * from databasename.dbo.SomeTable

It's the databasename part of that that seems to be causing the problem (although as far as I could tell, in my case the DB name isn't any different between the old DB connection and the new one in my case.) Amending the table queries to remove the databasename from the SQL solved the problem for me.

You can do this as follows:

• go to Database / Set Datasource Location in the menus.
• drill down in the report tree to the tables that are causing a problem
• Under Properties, click Overriden Qualified Table Name:
• In the text box, type the name of the table without the database name (e.g. dbo.SomeTable)
• Do this for all the tables causing a problem

(As a comment on that blog post points out, you could also create a new connection and replace the tables with the equivalents from that new datasource, but that leaves you with the fully qualified table name from the new connection - so you might get the same problem again in future.)

Answer 2

in Crystal, under file and options and database tab, the data explorer must have tables checked. This is not an easy feature to know about