Crystal Reports 9 set datasource location problem

I'm curious if anyone can tell me why I'm unable to set a new datasource location?

I have 4 identical databases in an SQL server where I'm wanting to use a single report to connect to each database to show data. Sounds simple enough, right? I'm not trying to do anything strange, just wanting to run the report 4 separate times to connect to each database individually to get 4 separate results.

When going through the usual steps (and I've done this many times before with other reports), I can see that the report is not working right because the 'show SQL query' results doesn't change. It looks like the datasource is getting changed, but no matter what I do, the <databasename>.dbo.TableName is 'stuck.'

The only way I've been able to get it to work, is to create 4 separate reports, which I'm wanting to avoid. I'm *thinking* the problem may be because this report was converted from 8.x, and it's left something embedded (the table alias, I suppose) which can't be changed.

Has anyone else had this problem and found a reasonable solution?

Also, just out of curiosity, does Crystal Report XI have this same annoying feature that you have to go through to set a datasource location, verify database, etc?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I'm thinking that the 4 different reports would be a better solution since you will avaoid having to manually change the database connections each time you need to run the report.

Anyway, here are the steps I use to change the database - might be the same as you're having a problem with - if so, let me know

1.  Select Database from the menu, set Datasource Location
2.  Create New Connection (if needed) - OLEDB (ADO) Provider for SQL Server
3.  Select the database you want to connect to and provide logon info
4.  Click on the table/view in Current Datasource section
5.  Click on same table/view in new database connection in the Replace With Section and click update
6.  Verify Database change in current datasource properties for the table/view
7.  Close

Now you should be able to see the changed database when you 'Show SQL Query'

There's another step that would need to be done if the tablename is different - let me know

Any chance you're running your reports from an application?  Then it's easy to dynamically change the datasource.
Agree.  If this is something you will do all the time and the report isn't likely to change then 4 reports may be the easiest.

If the report is likely to change then an application to run the reports is the best way.  If you want all 4 reports run each time, the application could run the report 4 times changing the database each time.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mk553Author Commented:
Thanks for the tips; unfortunately going through the steps doesn't actually solve the problem since the query string is 'stuck' no matter how many times I try to change the datasource, but again, I believe it's because the report is somewhat corrupt after having been upgraded from an earlier verion.

I'll have to use mlmcc's recommendation of writing a small program to change the underlying datasource at runtime, which I was also hoping to avoid. :-)
Glad i could help

Why a grade of C?  I also suggested using a program to dynamically change the datasource in the first place?  In addition to suggestintg an alternative manual method.  Just curious why I got such a bad grade...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.