SQL Server Performance Forum – Threads Archive

Trouble getting results from Linked server

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
FROMserver02.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. Now the query goes on forever until I cancel it. Even then it takes a long time, eventually reaching 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 check collate compatibility settings. Any ideas on how to get this working again. Thanks.

---

What was the problem or error message with the first query? /Argyle

---

It just never finished. Even after hours. The results were previously returned very quickly.

---

First of all check whether any network problems between 2 servers. Make sure that your largest column is the last column retuned. Make sure that you do not have any columns returned after a text or an image column. Satya SKJ

---

While the query is running do a profiler trace (include errors) on both servers to see if you see something suspicious like many slow sp_cursoropen statments. /Argyle

---

The trace indicates nothing is happening. There are no events coming thru. Its just hung.

---

Does any of the columns has TEXT datatypes? Satya SKJ

---

No. Theres nothing but simple data types. Thanks.

---

Got Microsoft working with my DBA on it. So far, the official line is ‘loop backup queries are no longer supported in 2000.’ However, so far their workarounds are not working either.

---

Here’s the answer folks. Our 2000 servers were created by imaging an original build. Consequently, they all had the same identifying ID. The Distributed transaction manager couldn’t tell the servers apart. Thanks for your inputs.

---

]]>