Passing Variable list from one OpenQuery to another OpenQuery

Question

New to asking questions, but have been using Stackoverflow for years.

I have two individually linked servers that do not speak to one another. I need to get a list of variables from one server into SQL and then pass that list of variables into the second OpenQuery with an end goal of bring the data from that server into SQL. I have been able to pass single keyed in variables. But when I try to use a stringed OpenQuery or just a stringed query it returns errors. Can someone point me in the right direction?

Current query:

    DECLARE @string VARCHAR(MAX)
,       @SQL NVARCHAR(MAX);

SET @string = 
'
Select 
    col1    
from 
    OpenQuery(server1, ''Select col1 from table1 where col2 = '''' a ''''
    ';
SET @SQL = 'SELECT * FROM OPENQUERY(Server2, ''SELECT * FROM table2 WHERE col1 = ''''' + @string  + ''''' '')';


EXEC sp_executesql @string;

Any Assistance you can give would be greatly appreciated.

I am using SSMS 2012 running on 2008 SQL servers. The two linked servers are Oracle servers not able to be remote connected.

Dave C · Answer 1 · 2014-02-25T04:13:51.310

By nesting one openquery within another, you are telling server2 to run an openquery to server1, which you have stated you cannot do. There are also errors above, in that you are nesting @string into @sql, but you are executing @string, not @sql.

If you are expecting a relatively small return, I'd suggest using temp tables to hold the data from server1, then filter the return from server2 using that.

IF OBJECT_ID('TEMPDB..#TMP') IS NOT NULL
    DROP TABLE #TMP

SELECT COL1 
INTO #TMP 
FROM OPENQUERY(SERVER1, 'SELECT COL1 FROM TABLE1 WHERE COL2 = ''A''')

SELECT * 
FROM OPENQUERY(SERVER2, 'SELECT * FROM TABLE2')
WHERE COL1 IN (SELECT COL1 FROM #TMP)

As a followup edit, if the return from server2 is large, you should consider filtering it before it returns to your local server so you're not transferring massive amounts of data over the wire. In this case, I'd convert the values of #tmp into a comma separated string, and nest that into the 2nd openquery in a variable, and execute it with sp_executesql. Here is a great SO post on converting a column to a comma separated list.

Thanks for the response. I had the execute swapped originally. I pasted iteration 19.Unfortunately I am returning roughly 1 million records from Server 1 (my population), and getting data from server 2(my data). My data server has approx 16 million records — Dacius, Feb 25 '14 at 14:24
There's no easy way to do this in MSSQL against two Oracle sources. I'd think your best approach is to have a proc on Server2 that you can call. If you absolutely must use MSSQL, then I'd run the select into #tmp from Server1, and then push the resulting #tmp data to Server2 as an insert into an Oracle user table (indexed?). Then you can select from Server2 via an openquery, and filter by the new user table which is now sitting on Server2. — Dave C, Feb 25 '14 at 15:57

Passing Variable list from one OpenQuery to another OpenQuery

1 Answers1