If the table is small enough, can you convert it to xml and send it to the other server?

-Mike

>I have two linked servers with collation compatibility set. The server to which I connect, call it server01, has a small temp table which needs to be joined with a very large table on the remote server02.
>I want to send the small temp table over the server02. Have the join occur on server02, return the small result set and insert that result set into a table on server 01.
>
>The following technique worked perfectly when server01 was a SQL 7 server. I started having problems when we set up a new SQL2000 server as server01.
>
>INSERT INTO ##MyTempTable
>SELECT * FROM OPENQUERY(benrosql03,'SELECT
>a.Field1
>,a.Field2
>FROM server02.my02db.dbo.BigTable a
>INNER JOIN
>OPENQUERY(server01,''SELECT * FROM ##SmallTable'') b
>ON a.Field1 = b.Field1')
>
>Note the inner Openquery will send the small table to the BigTable server02 where it will be joined with the big table. The results of the join are sent back to server01. This worked fine until we switched server01 from SQL7 to SQL2000. After the switch, it just goes on forever until I cancel it and then it takes a long time to cancel eventually reporting a timeout.
>
>In an attempt to work around this problem, I tried changing strategies. Rather than nested Openqueries, I tried to replace the outer nested query with a server02.master.dbo.sp_ExecuteSQL as below.
>
>DECLARE @cNSQL NVARCHAR(4000)
>DECLARE @cSP_ExecuteSQL VARCHAR(255)
>SET @cSP_ExecuteSQL = 'server02.Master.dbo.sp_executeSQL'
>SET @cNSQL =
>'SELECT
>a.Field1
>,a.Field2
>FROM
>server02.my02db.dbo.BigTable a
>INNER JOIN
>OPENQUERY(server01,''SELECT * FROM ##SmallTable'') b
>ON a.Field1 = b.Field1'
>
>-- INSERT INTO ##MyTempTable
>EXECUTE @cSP_ExecuteSQL @cNSQL
>
>If the INSERT INTO ##MyTempTable line is commented out as shown, the results return to QA almost immediately. If I uncomment that line, the query never finishes and when I finally cancel it, it takes a long time before it will stop and then gives the follwing messages.
>
>Query cancelled by User
>[Microsoft][ODBC SQL Server Driver]Operation canceled
>[Microsoft][ODBC SQL Server Driver]Timeout expired
>ODBC: Msg 0, Level 16, State 1
>Communication link failure
>
>Connection Broken
>
>So far I have not found any way to perform the join on server02 and get the results into a table on server01. As long as I am content to look at the results in QA, its fine. If I try to insert the result set into a table, my query never finishes until I cancel it. This was working beautifully until we change from SQL7 to 2000 on server01. Server02 has always been on SQL2000. We have checked collate compatibility settings.
>
>Any ideas on how to get this working again. Thanks.