Data from more databases
-
I need to make a report with a datasource from more databases.
Input parameter on a report defines a database, all databases have the same structure but different data.
I would like to use just one report for data from all databases (one database at a time) and give the users a chance to choose a database.
Is this possible?
Have a nice day!
Uroš
-
It can be done in different ways. I do it by creating the same stored procedure in each database, and have the database I want the data from as a parameter. So you can connect to one of the databases, but get data from the database you asked :
create proc dbo.getdata
( @dbname sysname )
as
begin
declare @proc sysname,@rc int
if db_name() != @dbname
begin
set @proc = @dbname + '.dbo.getdata'
exec @rc = @proc @dbname = @dbname
return @rc
end
/*
retrieve data from current db
*/
return 0
end
-
Very interesting and useful solution ...
Thanks for reply, Bart.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply