August 28, 2005 at 4:10 pm
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!
August 28, 2005 at 6:21 pm
What technology is used for the report? If it is a custom built report, part of your web application, just change the connection string each time the report runs.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
August 29, 2005 at 12:05 am
Well u can do it with Stored procedure ->send the db name as parameter.
+ in the sp use dynamic sql like: "SELECT * FROM " + @DB + ".tbl_name" ....
August 29, 2005 at 8:31 am
Better to avoid dynamic SQL if possible!
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
August 29, 2005 at 9:50 am
no problem, create a stored procedure where you UNION or UNION ALL the results together. then use the proc as the source data for your report.
Create Procedure MultiDatabase
begin
select 'DB1', field1, field2, field3,...
from database1.dbo.reporttable
union
select 'DB2', field1, field2, field3,...
from database2.dbo.reporttable
union
select 'DB13, field1, field2, field3,...
from database3.dbo.reporttable
end
You must have the same number of columns in each of the selects and the data types must match in each column. There is a way to get information from other servers using linked servers but i haven't played with that.
August 29, 2005 at 6:12 pm
Had a similar situation at one of my employers. We used a Custom Data Extension to get in between the report and the database, and re-build the connection string on the fly.
September 8, 2005 at 8:52 am
You need to clarify.
Will the report (instance) contain data from only 1 database at a time?
Or will it contain data from N number (2-9 in your example) of databases at the same time?
(Aka, will the report have data from db1 AND db2 AND db..N ) ?
Are you limited to just Sql Server? What are you coding in?
If I was doing this from scratch, I'd have a DotNet dataset object. and use the DataSet.Merge method (if I needed data from N number of databases at the same time).
If you only need data from 1 db at a time, then switch out the connection strings.
..
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply