I was helping a customer set the location of several tables from one database (or catalog) to another identical database on the same server. We tried to set the location using the ODBC connection, but they are on the same server so it didn’t change the catalog. We also tried logging out and logging back into the ODBC connection so that we could select a different catalog on the login screen. But this only changed the catalog shown in the connection properties. The table properties still showed the original catalog. When we refreshed the report the data still came from the original catalog.
So we figured we would have to set the location table by table. That would allow us to specify a table within the new catalog. But that is when things got weird. We exploded the dbo node and we couldn’t find any of the tables. Instead we found a single list with thousands of object names – none of them being the tables we needed. What was more confusing was that there were no nodes to indicate if we were looking at tables, views or stored procedures. We knew that the tables existed because they were used by other other reports.
So I went into Crystal’s “Database Options” (File > Options > Database Tab). This lets you specify a filter for tables Crystal would show. I was looking for a table that started with IV so I used %IV% as my filter. Then we opened the connection and now the nodes for Tables and views appeared. Each had a handful of entries that had IV in their names, including the tables we needed. But when I took the filter out, the nodes went away again, now there were no IV entries in the list.
When I showed this long list to someone more familiar with the data, they told me that the objects shown in the long list were all stored procedures. Then the light bulb went on. With a little research I found some things I had never run across before:
1) Crystal’s Database Expert can show up to 8,000 objects. (This can be changed in the registry – see SAP tech note 1215994.)
2) When Crystal hits that limit there is no warning, it simply ends the list.
3) If everything in the returned list of objects is the same object type (tables, views, SPs) then no nodes are generated.
I was surprised by items 2 and 3. If Crystal had told us that we had hit the limit we would know to filter the list. Or if they generated the SP node for the long list, it would have been clearer. Once we realized the that the SPs were the problem the fix was easy. On the same “Database Options” tab you can decide to expose or not to expose each of the three object types. When we took the check mark out for Stored Procedures we saw the nodes for tables and views, and everything was visible again.