UNION results of local query with SP on another server

[Earth]

I'm looking for an elegant solution for this problem:
I have two servers, Server1 and Server2.
I am attempting to write a stored procedure on Server1. Within this stored procedure, I want to UNION the results of a SELECT statement with a recordset that I can get by running a stored procedure on Server2.

Running the stored procedure on Server2 from Server1 is not a problem - the linked servers are correctly set up. I have no problems with the queries themselves - it's just unioning the results that I have problems with. I am assuming I can't do:

exec Server2.databasename.spName
UNION
SELECT statement...

The recordsets from the two servers meet the requirements for a union: ie. they have the same number of columns, same types, same names etc....

Thanks!

 

[dbtech]

yes you are correct you cannot do this
exec Server2.databasename.spName
UNION
SELECT * from #temp2

You can try this, Create a temp table which holds the result set of exec Server2.databasename.spName
assuming your sp returns fields f1,f2

Create table #temp(f1 int ,f2 int)

INSERT into #temp(f1,f2)
EXEC exec Server2.databasename.spName

once you have the result in #temp

u can try this!

Select * from #temp
UNION
SELECT * from #temp2

TIP
if the individual results from #temp and #temp2 are unique, you can use UNION ALL instead of UNION for better query performance


dbtech

 

[Earth]

Thanks dbtech.

I have actually tried what you mentioned. I have discovered that there appears to be a limitation in SQLServer that prevents you from executing a SP on Server2 from Server1, and putting the results into a temporary table (on Server1, obviously). You cannot execute the a SP on Server2 and put the results into a real table on Server1 either!

The solution I'm implementing for my problem which I hope is going to work (fingers crossed) is to run SP on Server1 that:

1. Executes a query on Server2 that:
a. Creates a table on Server2
b. Fills that table with the relevant data
2. Runs the:
SELECT Whatever
FROM tbl
UNION ALL
SELECT *
FROM Server2.tbl
On Server2....

Obviously I could just SELECT/UNION query directly on tables on Server2 - but the necessary query is extremelly complex, and I believe would take a lot longer than this way. Unless someone has a better idea?