Multiple DataBases single report

Ok Here's the senario,

I am currently designing a report which will be used in a production environment by multiple users. Now each of these users might be looking at a different production database, now i want to be able to create a single report that can be pointed at any one of these databases depending on which user calls the report. All the databases have the same structure but may contain different data, so in effect the query I am using can remain the same it is just the datasource connection info that needs to change dependant on the user and the database they are looking at. I could create multiple instances of the same report each with a connection to the different databases, but i would prefere to have only a single report.

I am considering writing a seperate App which the user could launch from within the production environment which would then pass the connection iformation to the report based on what ever user is running it, this however seems like a bit of a hack, Is there a better way of doing it.

Cheers

James R

Can you pass a parameter in when they run the report where the stored procedure will pull the info from the correct server?

For instance, if you had a dataset that contained the log-in user id and a code to identify what server to use (say 'A' would be Prod01 and 'B' would be Prod02)

Then in you could pass the server code to the stored procedure that pulls the data you could do something like

IF @.srvrCode = 'A'
BEGIN
SELECT *
FROM PROD01.DATABASE.dbo.Table
END

IF @.srvrCode = 'B'
BEGIN
SELECT *

FROM PROD02.DATABASE.dbo.Table
END

Hopefully that could offer some assistance

Multiple databases for each client of one big master DB?

We are designing a new data model, and have many clients. Currently they each have their own database in SQL.

We are contemplating the pro's and con's of migrating to a Single Master Database.

I would like to hear from others on what your thoughts are on the pros and cons. Also, if you have experience doing something similar, please speak up.

Thanks in advance,
ScAndal2 of the biggest cons I see are restore database ability and the ability to customize things on a per client basis.|||I was at this "fork in the road" at the beginning of this month. I have a system that is runs within an Application Sevice Model over the web. There are approx. 26 tables and 275 stored procedures for the system. database Our initial design was to have a differenct database for each client. There was one main controlling database which would administer the clients and their respective databases. The model was working fine and I even had the administration of creating new clients automatic. But the harsh reality of a maintenance nightmere was coming true. We have the potential of having 60,000 clients, which is equivilent to 60,000 databases and then aother 60,000 databases on another server on another coast that is used for replication. That is a total of 120,000 SQL Databases!

Though the reality of having that many clients will most likely never happen, it did bring up a very good point: what happens if you have to make one filed change a two stored procedure changes? What a nightmere!

We made the decision to modify the model to oeprate from one database which uses administration tables to administer the clients through the database. This model is more cost efficient an safe from corruption. It will allow easy replication and backup of logs. Though the data is now squashed into one Data Structure it makes sense to let the database do what it was desinged for: manage data. A good portion of your decision should also be based upon your possible client base. Determine the minimum number and maximum number of clients that you could potentialy have and go from there.

HTH|||Wes you bring up some great points. Currently our client base is < 20. I forsee us never taking on more than 100.

ScAndal|||One of the pros of the multi database approach is data security and integrity between client databases. This is why I initially designed the model using multi-databases. Each client login would utilize a different SQL Server login account that was specific to their database. Therefore if one of my clients network was penetrated by a hacker who then gained login access to the system and decided to distroy and corrupt the database, I would only have to explain to 1 client what had happend, and at worst lose 1 client--even though the hacker came through their network!. Whereas in the single database model I would potentially have to explain to 200 other clients, and possibly lose 200 clients, why their data is corrupt and the system is offline for thirty minutes while I restore from backups on the other coast (given the hacker was not able to jump to those servers). That is my strongest argument for the multe-database model.

Another aspect to look at is Administration of the Databases after development and deployment. Is your time limited and the databases will need to run without much intervention or will you be fully dedicated to these databases where you might get bored with them?

I would love to hear about experiences from others on this same issue.