October 6, 2013 at 6:11 pm
I need to create one report with 5 different data sets and from 5 diff data sources. All the 5 data sources are from 5 diff servers and they are not linked servers. So I am creating 5 data sets with 5 diff data sources and executing 5 diff stored procedures and getting 5 diff result sets with same column names and need to add all these 5 result sets into one report.
I need to union the result set of all the 5 Stored procs and use it one report.
How can I do this?
Thanks.
October 6, 2013 at 6:29 pm
You can't create linked servers and do the union in your stored procedure?
October 6, 2013 at 7:03 pm
No. I can't create linked server.
October 7, 2013 at 7:49 am
Perhaps this might help
http://technet.microsoft.com/en-us/library/ms188386.aspx
if it can be near time, could you also not configure a SSIS package to consolidate the data into one location?
October 7, 2013 at 8:04 am
Well if all else fails and you don't need to combine the data and sort it, why not create 5 tables and make sure that the tables positioning on the report leave no spaces between the tables, It will look like 1 table but not function as 1 table
October 7, 2013 at 8:54 am
one way to do it could be to have a master report with 5 subreports each pulling a resultset. If you need to manipulate the resultset I don't know if that single report will allow for it though. Another possibility (that might be overkill) is to create a library in .NET that sets up those connection strings and pulls the results into a single resultset then returns the result to the report.
In either case, you might want to re-examine the workflow that makes this necessary. It might be legitimate, but it sure feels wrong...
October 8, 2013 at 6:09 pm
Like one of the other poster says, try using a stored proc.
You can use the OPENROWSET approach to create (in-effect) an on-the-fly linked server for each server connection.
And then UNION all the individual queries together. This sample works for me -- using just 2 servers. Could easily extend it to five.
-- List the databases on several different servers
SELECT A.*
FROM OPENROWSET('SQLOLEDB', 'SERVERA' ; 'sa' ; 'SAPasswordA',
'SELECT * FROM master.dbo.sysdatabases') as A
UNION
SELECT B.*
FROM OPENROWSET('SQLOLEDB', 'SERVERB' ; 'sa' ; 'SAPasswordB',
'SELECT * FROM master.dbo.sysdatabases') as B
A couple of notes here:
1. You may not want to use the "SA" -- and even if you do, you probably should not hard-code the SA passwords in your proc [Bad practice]!
2. You may want to retrieve just the columns you want, and not do the "SELECT *".
-john
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply