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.

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 Database parameter question

Author  Topic 

singerji
Starting Member

1 Post

Posted - 2008-02-25 : 16:24:14
I have several databases on the same server that have the same table structures within them. Call them

Server.A
Server.B
Server.C
...etc.

I have designed a report that works in one database, say "A". I would like to add a parameter box to the report giving the user the ability to select which database to run the report in. I'm not sure if this is possible.

There may be more complex ways to handle this, say, with dynamic SQL or by building a large UNION of select statements across the various databases. But I figured I might ask for a simple solution first.

dineshasanka
Yak Posting Veteran

72 Posts

Posted - 2008-03-03 : 12:08:00
You don't have to use UNIONs

if @databaseName = 'ServerA'
SELECT * FROM ServerA.dbo.Table1
Else
if @databaseName = 'ServerB'
SELECT * FROM ServerB.dbo.Table1
ENDIF

But i beleive this very dificult to manage if you have large number of databases.
This will ok for 2-5 databases but not for many.

---------------------
http://dineshasanka.spaces.live.com/
Go to Top of Page
   

- Advertisement -