1 report for 9 databases | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

1 report for 9 databases

The web application I have has nine databases (and it’s only going to get larger in number), and for each database we have the same report, but the dataset is set to that database’s table. What I’d like to do, and I’m almost sure it can be done, is look at the session object when the user logs into the application, and see which database they’re using from a dropdown list they choose on the first page. With this db session object, I’d like to pass a parameter for the report to point to that database table. This way, I only have 1 report, rather than 9. Is this possible? I can’t imagine it isn’t! I’ve got the HitchHicker’s Guide, and if it’s in there, I’m totally overlooking it! Thanks!
You dont say what technology youre using to connect, but lets assume its ADO.NET or something. The connection string can specify the database. Alternatively you can consider placing the stored procedure in the master database, and prefixing it with ‘sp_’. This way you can invoke it from any database and it will call the stored proc in the master database, but will execute in the context of the current database.
]]>