Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
harlingtonthewizard
Constraint Violating Yak Guru
352 Posts |
 Posted - 2008-07-22 : 06:26:09
|
| I have created some reports in Reporting Services 2005 and currently connect to a single shared data source. Our developer wishes to archive databases periodically to get around the 4GB limit in Express Edition. I understand I can change the reports to use an expression that is used to select the data source at run time. However, how can I run a report against multiple databases at the same time? Basically so the user sees a single report result but the data is coming from the active and multiple archive databases? Please be gentle with responses as I am new to SQL etc. |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-07-22 : 08:04:59
|
| This sounds like a fairly complicated solution. For the time & effort involved a standard edition may be a better investment. If you still want to continue with it, see http://www.sqlservercentral.com/articles/Development/2945/ for dynamic data source. But it sounds like you want the one report to pull data from multiple databases at the same time. You can reference different servers & create a union egselect businessTypefrom server1.adventureWorksDW.dbo.dimResellerunionselect businessTypefrom server2.adventureWorksDW.dbo.dimReseller |
 |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-07-22 : 08:07:40
|
| If you don't want to change the code every time you archive to a new server, you'll then have to make it dynamic perhaps putting the server names in a table & constructing the dynamic sql based on this - sounds very messy. |
|
|
|
|
|
|
Reporting on multiple databases at the same time