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!


  • 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 )



     declare @proc sysname,@rc int

     if db_name() != @dbname


      set @proc = @dbname + '.dbo.getdata'

      exec @rc = @proc @dbname = @dbname

      return @rc




      retrieve data from current db



     return 0


  • Very interesting and useful solution ...

    Thanks for reply, Bart.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply