none
Can I run one report against multiple databases?

    Question

  • I have a stored procedure that is stored in multiple databases and I want to give the client the option to pick the "database" from a drop down box of the report then pass that parameter to my stored procedure to run against the chosen database.

    Is this possible and would I have to use dynamic sql for passing the parameter to my stored procedure?

    Thanks,

    Sue


    Sue

    Tuesday, October 29, 2013 3:11 PM

Answers

  • Hi Sue,

    If I understand correctly, you want to use dynamic Data Source in your report. Just as you thought, we can use expression-based connection string to create the Data Source, and create a parameter to select “database” from the drop-down list, then pass parameter to stored procedure to run the report. But in order to achieve the goal, the table which used by stored procedure should be with same table definition codes in those databases.
     
    The following steps which I tested is for your reference:

    1. Create a DataSource with the expression-based connection string below:
           ="Data Source=.;Initial Catalog=" & Parameters!DB.Value
    2. Create a parameter named DB with two available values below:
      Label: database1    value: =”database1”   Label: database2    value: =”database2”
    3. Create a table in each database with same table definition codes (Don’t need same data in the table).
    4. Create a same stored procedure named sp_test in the two database.
    5. Create a DataSet with the database1 as DataSource, then select sp_test stored procedure to return the data.
    6. Change the DataSouce to DataSource1.


    The following screenshot is for your reference:
                      
    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 31, 2013 1:55 AM
    Moderator

All replies

  • Hi Sue,

    If I understand correctly, you want to use dynamic Data Source in your report. Just as you thought, we can use expression-based connection string to create the Data Source, and create a parameter to select “database” from the drop-down list, then pass parameter to stored procedure to run the report. But in order to achieve the goal, the table which used by stored procedure should be with same table definition codes in those databases.
     
    The following steps which I tested is for your reference:

    1. Create a DataSource with the expression-based connection string below:
           ="Data Source=.;Initial Catalog=" & Parameters!DB.Value
    2. Create a parameter named DB with two available values below:
      Label: database1    value: =”database1”   Label: database2    value: =”database2”
    3. Create a table in each database with same table definition codes (Don’t need same data in the table).
    4. Create a same stored procedure named sp_test in the two database.
    5. Create a DataSet with the database1 as DataSource, then select sp_test stored procedure to return the data.
    6. Change the DataSouce to DataSource1.


    The following screenshot is for your reference:
                      
    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 31, 2013 1:55 AM
    Moderator
  • Hi Katherine,

    Thank you, I'll try it on my report and let you know if I have any further questions.

    Sue


    Sue

    Monday, November 04, 2013 9:13 PM
  • Hi Katherine,

    Sorry, I was pulled off into another project or 3 and I am just getting back to this. I have looked at the example above and I am not sure that it is exactly what I need. I may be approaching it wrong in my report but let me tell you what I've done so far.

    I have over 30 databases that I need to pull the same data from for this report. On each of those 30 databases I have the same stored procedure.

    In a "master report" database I created another stored procedure that pulls all of the client names and database names from the other databases at run time.

    Example:
    ======

    ABC Client  - client name and 001-ClientDatabase - database name

    In my report I have a drop down list that shows the user the client name but I have hidden in the same row the database name that I need to pass to my dynamic sql in the "RunClientReport".

    In my "master report" database I have yet another stored procedure that calls the client stored procedure by passing the "database name" to the stored procedure and uses dynamic sql to apply the database name to a "use" statement to call the stored procedure in each of the 30 databases. See the code below.

    CREATE PROCEDURE [dbo].[spRunClientReport]
    
    
    
    @DatabaseName 
    
    nvarchar(255)
    
    
    
    AS
    
    BEGIN
    
    DECLARE
    
    
     @sql nvarchar(4000);
    
    
    DECLARE
    
    
     @dbName nvarchar(255);
    
    
     
    
    
    SET
    
    
     @dbName = @DatabaseName
    
    
    SET
    
    
     @sql = 'USE ' + @dbName + '; EXEC sp_executesql N''[dbo].[spClientReport] ''';
    
    
    
    EXEC
    
    
     sp_executesql @sql
    
    
    
    Return
    
    
    (0)
    
    
    
    END
    

    I cannot seem to pass my parameter to my stored procedure "RunClientReport"  in my report and I don't know why it isn't working. I keep getting different error messages as I try different things to get it to work.

    Can you point me to a website or give me some tips on how to pass the database name to the "RunClientReport" or tell me if there is a better way to do this?

    Thanks,
    Sue


    Sue

    Tuesday, February 18, 2014 4:57 PM
  • I finally figured out how to pass my database name to my stored procedure.

    Thanks,

    Sue


    Sue

    Wednesday, February 19, 2014 6:09 PM