Select database at runtime

[simpelli]

Greetings,

Hopefully I can express this situation clearly...

We're creating a portal to which customers will subscribe and create and store data.
Each customer will have a login to the portal
Each customer will have a matching Active Directory account login
Each customer will have their owh SQL server 2000 database, named the same as their login.
The site would be similar to a SharePoint site - but it's not SharePoint. All custom asp.net


When they login to this portal they are directed to their database basd on their credentials.
So we have

AD user login CustomerA
Portal user CustomerA
Database CustomerA

We want 1 set of reports availabale to all customers to display their own data in the same format. (Each database schema is identical - just the data is different)

I've been tasked with looking into the reporting tool. The lead developer is pushing SQL Reporting service (2003) They are really tight with MS so it's likely this is the direction we're going. I'm not too familiar with it, but from what I've seen, it's not real obvious whether the report source can be changed on the fly like that. I imagine it can <grin> but...

Anyone know of any resources in this area or better yet - have you done it?

Thanks

-S

 

[Catadmin]

Haven't done dynamic database sourcing yet.

The one thing I've noticed in RS 2000 is that you have to have your data source actually point to a database or it complains. So, the first thought off the top of my head is to create a series of data sources for each customer and then create a series of data sets pointing to those data sources. Of course, that could get really messy really fast.

However, I've only been working from the Report Designer view of things. I haven't actually been using RS from the VB / C# POV of coding, so there might be some possibilities there that I am unaware of.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"