one or many sprocs

  • I need to create a report in SQL Server Reporting services that displays data from several databases.

    Should I create one stored procedure that contains multiple select statements and UNION ALL them together, create db specific stored procedures and combine the data in SSRS or is there another recommended approach to this type of problem?

    TIA

    Dean

  • I had to do something similar to monitor a variety of field SQL Server nodes that I have very limited access to.

    I created an SSIS routine on one of my department servers. In the SSIS package I created connection strings for each destination node. Then I created control flow, running the same query against each node. The results are stored on the same local department servers, which I can run reports against. this approach means i don't have to manage usp's across many nodes. All the logic is stored on my department server.

     

  • If you have this option it is the way to go.  Are the db's all on the same server?

    I would write db specific sp's and then have a master sp in one of the db's that executes the other sps like

    Create procedure sp_data

    AS

    Create table #table ()

    Insert Into #table

    Select

    From

    tables

     

    Insert into #table

    Exec db1.dbo.sp_data

     

    Insert into #table

    Exec db2.dbo.sp_data

    ...

    Select * from #table

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

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