Automating “Database > Set Location”

Thursday 16 July 2015 @ 11:34 pm

One of my customers has about 50 reports that all point to the same SQL Server database. When they moved their database they needed to “Set Location” for all 50 reports. Normally you can set all the tables from one connection to another in one step by updating the old connection to the new connection. This should work as long as all the tables have the same name.

But in SQL Server, Crystal sees the table names like this:


So if the name of the database is changed Crystal thinks the table name is different. So when this user tried to “Set Location” at the connection level, she got “invalid object” errors. She learned that the name of the database had been changed when setting up the new server.

When Crystal can’t find a matching table name in the new connection, you have to map each table in the report to the corresponding table in the new connection and update them individually. With 6-8 tables in each report the process would take a few hours. I thought we might be able to fool Crystal into just looking at the table portion of the name, but after trying a few things it didn’t seem like we were getting anywhere.

So I suggested that she check out the report management utilities on my annual comparison. There happens to be one that is only $99, CR Data Source by R-Tag, and it is designed to do just that one thing – Set Location. I did wonder if it would have trouble with the database change but the customer reported back that it did the job and saved her several hours of tedium.

One Response to 'Automating “Database > Set Location”'

  1. Ken Hamady - July 21st, 2015 at 11:43 pm

    The following comment from Bob Antaki:
    The way I deal with our multiple SQL Server databases, which have identical table structure, is to do a one-time change to each table in the report. In Set Location, for each table, in the Overridden Qualified Table Name field, I enter the table name.

    After that is done once, then I can change the database name in Set Location by using the Update button. When I found out about doing this, it saved much, much time, since I did not have to change each table individually – and we have a lot of reports.

    Thanks Bob, That was one of the things I tried. For some reason CR still couldn’t find the tables. But I didn’t spend much time on it. It would have required touching each table in each report, and since this update wasn’t likely to be repeated there was little gain down the road. But I can see how this would be a big win in your situation.

Leave a Reply

Recrystallize Pro

Crystal Reports Server