More than one database RRS feed

  • Question

  • Hi,

          In oure reports we have Start Date and End date as parameters.And now our team's made the three different databases DB2007 contain current year data and DB2006 for 2006 data(Previous year' data) and DBHist (contain all previous years data).We have written a cistom assembly whick will return the DB name based on start date.And in the dataset we are calling Dbname..Stored procedure name.(db name coming from custom assembly).This approach is fine when we run the report with Dates with in one year suppose Start date:5/1/2006 EndDate 12/1/2006 Calls the Staored procedure in DB2006..SP name.

    If we run the report with this dates Start Date:6/1/2006 End Date:3/1/2007 our custom assembly in this case returns the DB 2006 based on Start Date 6/1/2006, in this case we are missing the 2007 data.

    One approach is use unions in the sp like

    Select .. from DB2007.Customer c

    Where c.startdate between @StartDate and @EndDate


    Select .. from DB2007.Customer c

    Where c.startdate between @StartDate and @EndDate

    Select .. from DB2006.Customer c

    Where c.startdate between @StartDate and @EndDate


    Select .. from DBHist.Customer c

    Where c.startdate between @StartDate and @EndDate.

     I think this approach will give very very low performance other than this getting the data from one database is really fast.Am i correct?

    How to call the Stored Procedure from two different databases in this case.

    Thanks in advance

    Thursday, March 8, 2007 8:30 PM

All replies

  • Hi,

    I think it will be easier to handle this problem within a sql procedure instead of trying to handle within the Reporting Services.

    You can place the main sp in a prearranged database. And according to the input parameters this sp may run some other sp's or may run a dynamic query which will union results from two different db's in the same instance if necessary.



    Friday, March 9, 2007 7:08 AM
  • Another approach would be to have 3 datasets which will call all the 3 DBs with the same set of date parameters.

    And have 3 tables in your report desinger (placed exactly next to each other, if needed) and handle the visibility of the table (and headers, footers etc) based on the count of that respective datasets.

    Because the date range may across all databases, say if start date='01/01/2004' and end date='01/01/2007'


    Friday, March 9, 2007 9:41 AM