Dynamically switching report data connection at run time

We have been presented with the problem of using Reporting Services to run
the same report against one of two databases.
We are currently using SQL Server 2000, and while 2005 has support for
dynamically building connection strings with parameters, 2000 apparently does
not.
Thus far, we have found the following solutions:
- A custom data processing extension, which wraps up a SqlConnection, and
switches database context at run-time based on an expected query parameter
- Reporting against a front-end query on the database, which in turn calls
the query from the desired database
- Installing two versions of the same report on the report server, and
having the application choose which to execute at run-time.
Each of these options has various drawbacks, the first brings with it a mess
of support and deployment issues, the second leans on the database harder
than it needs to, and the third is basically redundant.
Although the solution we need now is to switch between one of two databases,
the ideal solution would be able to manage 1-n databases.
We would appreciate your input as to which solution is the best, or if there
is functionality which would better suit our needs that we havenâ't discovered
yet.On Nov 16, 4:07 pm, breedReed <br...@.community.nospam> wrote:
> We have been presented with the problem of using Reporting Services to run
> the same report against one of two databases.
> We are currently using SQL Server 2000, and while 2005 has support for
> dynamically building connection strings with parameters, 2000 apparently does
> not.
> Thus far, we have found the following solutions:
> - A custom data processing extension, which wraps up a SqlConnection, and
> switches database context at run-time based on an expected query parameter
> - Reporting against a front-end query on the database, which in turn calls
> the query from the desired database
> - Installing two versions of the same report on the report server, and
> having the application choose which to execute at run-time.
> Each of these options has various drawbacks, the first brings with it a mess
> of support and deployment issues, the second leans on the database harder
> than it needs to, and the third is basically redundant.
> Although the solution we need now is to switch between one of two databases,
> the ideal solution would be able to manage 1-n databases.
> We would appreciate your input as to which solution is the best, or if there
> is functionality which would better suit our needs that we haven't discovered
> yet.
I would personally create a SQL Server instance that has Linked
Servers to your two other databases, then in reporting services pass
the SELECT * FROM OPENQUERY( @.ServerName, 'SELECT real SQL here' )
-- Scott