Home > Dynamics SL (Solomon) Technical Info > Crystal Reports – tables not found during Verify Database

Crystal Reports – tables not found during Verify Database

If you try to do a Verify Database on a Crystal report used by Dynamics SL, you may get an error message like this:

“The database table “GLTran” cannot be found. Proceed to remove this table from the report?”

even though GLTran (or whatever table name is in the error message) is in the database.

I have found this to be usually caused by Crystal  using Fully Qualified table names.  If you click “Show SQL Query” (or run a SQL Profiler trace while the report is running), you may see entries like “select * from databasename.dbo.GLTran”, instead of “select * from GLTran”.  The databasename may be the name of the SQL Server used when the report was originally written, which is usually not a server on your network.

In older versions of Crystal Reports, you could edit the SQL query in this dialog box to get rid of the database name, but in Crystal Reports 10, the SQL query dialog is read-only.

The way to do this in Crystal 10 was not intuitive to me, but here’s what you can do.  From the Database menu, choose Set Datasource Location.

Click the Overridden Qualified Table Name line and a textbox will appear to the right.  In that textbox, type the table name, just as it shows in the Table Name line.  (Account in this example.)  Do this for each table in the report and click Close.

Putting a value in the Overridden Qualified Table Name textbox seems to tell Crystal to use what you typed there for the table name instead of the Fully Qualified table name.

Save your changes and you should be able to Verify Database without getting the errors any more.

Advertisements
  1. June 18, 2011 at 1:36 am

    While your prescribed method will work, a better way to do this that is GUI driven is to use the Set Location menu item as you started with but then use the replace with window (bottom frame), create a new connection using ODBC (which is what Crystal Reports utilizes) and open an ODBC connection to your actual database. Drill down to the table view. Once you have the tables exposed in the bottom frame, click on a table or view in the top frame to highlight it and then click on the same table name in the bottom frrame in your desired database and click the Update button. Repeat this for each table used in the top frame. If your report includes subreports, you will need to drill down to those tables (or views) and perform the same steps. This will change the dbo association with each table or view in your report to be with your database.

    • June 18, 2011 at 5:25 pm

      Good point Rick. Thanks for chiming in.

      I will confess that some of my habits related to SL practices were set years (and versions ago) so there are probably some things I do to avoid bugs/problems that existed a long time ago and may have been fixed by now.

      I believe the method you describe will change the SQL statement from “select * from database1.dbo.tablename” to “select * from database2.dbo.tablename”, where database1 exists only on an SL dev server somewhere and database2 is your database.

      I actually prefer the SQL statement to be “select * from tablename” and to only get the database name from the connection. If you’re running an installation where there’s only 1 application database, it probably doesn’t matter much. Expand the scenario to include a database3, where database2 and database3 both exist on your server.

      In this scenario, I have seen SL choke when needing to change the statement at runtime from database2 and database3. Again, this was probably a long time ago, but old habits (and suspicions) die hard.

      My recollection is that SL changing from database1 to database2 at runtime was not a problem (since database1 wasn’t available at my site), but that sometimes since database2 was available, the redirect to database3 didn’t happen correctly. It was almost like it tried the linkage that was there, and only if that wasn’t available, did it explicitly redirect to the currently logged in application database.

      That’s probably a lot more than you wanted to hear on that, but I thought I’d explain why I use the method I describe.

      Thanks for reading and I appreciate the comments and feedback.

      JT

  2. ziyad Meeralam
    December 27, 2012 at 1:43 pm

    OMG MAN , THANK YOU a MILLION TIMES . i was trying to Solve this Issue like forever and finally found your great yet simple solution

  3. Josh
    June 14, 2013 at 1:51 pm

    It seems to be with the SQL Native ODBC Driver. I was getting this error with it and switched back to the normal SQL ODBC driver and then it worked.

  4. May 28, 2014 at 3:02 pm

    Thank you very much, big help when changing Datasource Location. Follow above instructions even with alias tables, similar with Command statements.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: