• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2019
  • Last Modified:

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?
  • 2
  • 2
2 Solutions

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.

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...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now