Hi all,
I have seen this question asked many times, but never an answer posted.
Here is the situation: I need to connect dynamically to an unknown
number of databases at runtime, which the user will be no part of (any
parameters involving the Servers or DBs will be internal/hidden). The
data in each of these databases will then be consolidated and
represented in a single dataset. We are using SQL Server 2005.
Is there a way to use multiple datasources (i.e. get multiple tables
from separate databases/servers) and use them all in the same dataset
without using stored procedures? I know this can be done in Crystal,
but as yet there is no documentation on how to do this with SQL
Reporting Services. Are there any articles or examples out there of
this that do not involve consolidating the data into another database
first or using stored procedures?
Thanks!
Lance MOkay, through some "suggestiongs" this is what I have found out. There
are about 3 ways of dynamically creating this dataset (there are more
but these are the easiest and most useful ways), each of them trickier
than the last.
1)The first way is to use the SQL Server Linked Server feature. I was
told to research other options XD.
2)Use embedded code to go in and access the databases and return a Data
Reader or something. This is okay, but a pain to have to add to
multiple reports.
3)and the winner is... custom Data Processing Extentions(DPEs). DPEs
are what you use to make connection strings in the first place, and
there are ways to make your own that can take variable paramaters and
do all sorts of nifty things for you. This way is very difficult (the
first time or if you have forgotten how) because you have to make the
server trust it, and from what I have heard MSDN documentation on this
subject is incorrect. The actual coding itself shouldn't be a problem
for a moderate developer.
Option 3 is what I am investigating, I will post an update as to what
my final solution is.
Lance M|||I have a need for the same functionality. Have you had any success with
option 3?|||Okay, I have finished testing (for the most part). Provided that I can
install the DPE everything should work perfectly (knock on synthetic
wood). I created the 6 or so class files required for a DPE, but most
importantly I managed to connect dynamically to the databases and
return a table made of the elements of data queried from the DBs. This
table will then be passed to the ExecuteReader method of one of those
classes, and if I followed any samples (that had poor/no documentation)
right, then I should be able to summon those fields within any report
that uses a dataset based on that DPE. I also have a grand hope that I
will be able to somehow execute stored procedures with my DPE as well,
thus making it more robust and user friendly (haha).
One issue that I would like to note is that if the table you are
building happens to be gargantuan, the report could be incredibly slow
or just refuse to show up even fashionably late. In these cases, you
may want to have the report run overnight, and use a snapshot that is
somewhat up to date for report viewers. If you absolutely must have
these types of reports running any time a user selects them, I suggest
you look for a new career.
Good Luck!
Lance M
