Changing Databases

I have two databases on the same SQL server which have identical data
structures.
I want to be able to use one set of reports on my report server to run
against either database. How can I achieve this? Whenever I run the report
it always runs against whatever the datasource points at.
NickWhat you do is write a stored procedure for your sql need in third database
other than those two and write in a way that it can accept database name and
then fully qualify your object name and build a dynamic sql in the proc. Use
this proc in RS to buid one dataset and create another dataset with simple
query like "select dbname from sysdatabases where <criteira of your
interest>". Use this dataset as a input to the parameters of the first
database so the final report will have at least one parameter to pick a
database name from the dropdown list and you can run your report off of
selected database. I have implemented this way and I am running my report
off of more than two database from one server and it works great. Let me
know if you have any difficulties. email me.
Vipul Shah
"Nick Jacobs" wrote:
> I have two databases on the same SQL server which have identical data
> structures.
> I want to be able to use one set of reports on my report server to run
> against either database. How can I achieve this? Whenever I run the report
> it always runs against whatever the datasource points at.
> Nick
>
>

Changing database at runtime

Hello,
I have 2 identical databases (identical structure) on the same sql server
correspoding to 2 companies.
Do I have to create 2 identical sets of reports or it is possible to choose
database on runtime?
Thank you,
LoriIn SQL 2005, you can make a dynamic connection, but not in sql 2000... about
the best you can do is use
Dynamic SQL, and OpenRowset or a linked server to do your queries ie.
If Parameters!.DataSource.value = 'a'
Select * from mytable
Else
Select * from openrowset(all the parameters that get you to the other
server...)
You could also put this in a SP... I am currently working on a project which
has about a dozen servers. We are making stored procedures which use the
openrowset function based on a parameter sent to the sp , which comes from
the Report Parameters.
As long as there are no cross server (multi server) joins etc. performance
has been alright...
I would prefer to use a linked server however... to centralize the login and
server info in one place instead of all over..
good luck...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Lori" <lhaiducescu@.seniorsoftware.ro> wrote in message
news:ev47$5ZoFHA.2920@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have 2 identical databases (identical structure) on the same sql server
> correspoding to 2 companies.
> Do I have to create 2 identical sets of reports or it is possible to
> choose database on runtime?
> Thank you,
> Lori
>