SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Combining two data sets into the same table?


Combining two data sets into the same table?

Author
Message
kramaswamy
kramaswamy
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3819 Visits: 1825
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.
davoscollective
davoscollective
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1629 Visits: 1008
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search