Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Combining two data sets into the same table? Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2012 2:02 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
Hi all,

Got the following situation:

Server A contains information about a particular field of a table that I want to populate for a report. Server B contains other information. I want to combine these two sets of information into a single table in a report.

The current solution I'm going with is to use SSIS to combine these two sets of information into a single database table, and then use SSRS to query this table for the report.

I don't really like this solution though - I personally hate to use static database tables to store snapshots, since it doesn't permit changes to the source data to be reflected in the report.

Does anyone have a better solution for me? And note that having a single stored proc / query using a linked server to join the two sources is not an acceptable solution for this problem.
Post #1381709
Posted Tuesday, March 5, 2013 7:34 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:03 AM
Points: 450, Visits: 842
kramaswamy (11/6/2012)
Hi all,

Got the following situation:

Server A contains information about a particular field of a table that I want to populate for a report. Server B contains other information. I want to combine these two sets of information into a single table in a report.

The current solution I'm going with is to use SSIS to combine these two sets of information into a single database table, and then use SSRS to query this table for the report.

I don't really like this solution though - I personally hate to use static database tables to store snapshots, since it doesn't permit changes to the source data to be reflected in the report.

Does anyone have a better solution for me? And note that having a single stored proc / query using a linked server to join the two sources is not an acceptable solution for this problem.


There's not really any other option. SSRS won't combine datasets for you so you need to combine them elsewhere such as in SSIS as you are doing or in a query. Why is the linked server option not acceptable? The only way to make sure the data is not a snapshot is do query it live.
If perhaps you don't have permission to create linked server objects on those two source database, what about using a third database as the launch point to query the other two? If you created a reporting/utility database like that you would have full permission to create whatever objects (linkedserver to the source databases) you like on there, of course being careful to ensure it is backed up with the rest of your backup policies.

Another possible option is to use multiple datasets in a tool like Excel PowerPivot and then mash them together in a pivot table. There's also other reporting tool options like Tableau that do multiple datasets very well but I suspect if you're on SQL 2005 then that's not an option.

Post #1427125
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse