How to create a single database connection for two different databases in the same server

[Chuku]

Hi,
I have a database ( say master) and another database ( say reporting) which are two different SQL databases present in the same server. Now I need to add tables from both of them to pull data into CR. But the requirement says I need to use a single DB connection. How do I do that?
One way is to use subreport to access one database in the main report and the other in the subreport and then link the two in subreport links. But I need the fields to pulled in the same detail section in the main report.
Please let me know how to proceed

 

[pmax9999]

When you say "... the requirement says I need to use a single DB connection ...", whose requirement is that? While Crystal will complain and say that multiple data sources are not supported, it does work.

When multiple data sources are used you will find that the report is very slow because the joins between databases will not be processed at the database server. This means that data from both databases will be passed to the local PC where the joins will be processed and any data not required will be dropped.

Hope this helps.

Cheers
Pete

 

[PeterDimitrov]

You can use a connection to one of the databases and create a view or stored procedure to retrieve data from another database. You can also join tables from the 2 databases. You can even work with databases from different servers (if they are linked). Sample format for a query joining data from 2 different databases is:

SELECT .... FROM database1.dbo.table1 t1 INNER JOIN database2.dbo.table2 t2 ON t1.field1=t2.field2

I am using dbo because this is the default schema . You might need to use different schema.

Once you create a view in database 1 you can completely ignore the second database and use the view with connection just to database1

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[lbass]

Do the two databases have the same fields? You could potentially create a command using "union all" to "merge" the corresponding fields (same order) into one field, as in:

select "table"."field1", "table"."field2"
from "database1"."table" "table"
where ...
union all
select "table"."field1", "table"."field2"
from "database2"."table" "table"
where ...

-lb